Custom Sorting Issue in Column Chart

I have written Case statement to reflect the same order but still the order is missing which you can see in column chart as per statement below:
CASE WHEN
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) < 0
THEN ‘<0:’
WHEN
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) = 0
THEN ‘Same Day’
WHEN
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) = 1
THEN ’ 1 ’
WHEN
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) = 2
THEN ’ 2 ’
WHEN
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) = 3
THEN ’ 3 ’
WHEN
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) = 4
THEN ’ 4 ’
WHEN
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) = 5
THEN ’ 5 ’
WHEN
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) > 5 AND
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) <= 10
THEN ‘6-10’
WHEN
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) > 10 AND
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) <= 15
THEN ‘11-15’
WHEN
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) > 15 AND
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) <= 20
THEN ‘16-20’
WHEN
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) > 20 AND
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) <= 25
THEN ‘21-25’
WHEN
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) > 25 AND
(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS)) <= 30
THEN ‘26-30’
ELSE ‘>30’
END

Please find the output after giving the above code, I dont know why blank is showing 11 count and <0 has to start and end with > 30 but it is not happening

In addition, i need the below points to be addressed:

  1. – are showing in the graph eventhough i have not stated in case when statement
  2. Can i get fixed x axis instead of dynamic

Hi Hari,

Thanks for posting your question here.

First of all, the sorting does not work as intended since you are defining your own brackets which are then considered as a string and not as an integer. Thus, “6-10” comes after “26-30” since it is alphabetically behind. A solution here would be to trick the system by adding varying amounts of blanks, depending on your intended order. They will not show up in the diagram, but define the sorting for the system, e.g.

WHEN …
THEN ’ 16-20’
WHEN …
THEN ’ 21-25’
WHEN …
THEN ‘ 26-30’
ELSE ‘>30’
END

Regarding the values grouped under ‘-’, this should normally not be case when using an ELSE statement correctly (like you did). Maybe there are 11 cases for which the amount of days cannot be calculated due to one of the input parameters not being a number?

I am not sure what exactly you mean by “fixed x-axis”. If you don’t want the component to be scrollable, this can be achieved by deselecting the scrolling checkmark in the component settings.

As a further advice, you could group the statement ‘(REMAP_TIMESTAMPS(ROUND_DAY(<%=Variable_4%>), DAYS) - REMAP_TIMESTAMPS(“VBAP”.“ERDAT”, DAYS))’ as a formula or variable, so the code is shorter and easier to read.

Best regards,
Your Data Science Team

I have sorted out the issue with other approach. Fixed X-Axis means Fixed values to be given at x-axis Irrespective of data points exists or not on these x-axis. While Case Statements shows only x-axis where data points exists. Hope this helps to clarify your query