Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I have a date field 'orderdate' which is having data in the format 'DD/MM/YYYY hh:mm:ss'.
I want to design a bar chart with Hour as dimension and sum(sales) as measure.
But user wants to see the hour representation in this format 'MMMDD-hh' (Ex: Nov14-06).
For this, I have written
Month(orderdate)&Day(orderdate)&'-'&Hour(orderdate) as Hour.
Here the issue is with SORTING. I am not able to sort it correctly in chart.
Could you please suggest a way to resolve this sorting issue ?
and also is there any other way to get the hour in the required format with NO sorting issue ?
Thanks in advance
Regards
BMK
May be create a new field for Sorting
(Month(orderdate)*10000) + (Day(orderdate)*100) + (Hour(orderdate)) as HourSort
May be create a new field for Sorting
(Month(orderdate)*10000) + (Day(orderdate)*100) + (Hour(orderdate)) as HourSort
WoWWWWW..
It worked..
Thank You!! 🙂 🙂
Use
TimeStamp(orderdate, 'MMMdd-hh')
or better, to ensure only one value per MMMdd-hh value:
TimeStamp(Round(orderdate, 1/24), 'MMMdd-hh') //nearest hour
or
TimeStamp(Floor(orderdate, 1/24), 'MMMdd-hh') // last complete hour
Now you can just sort numerically.
Hi mohan,
try with sort by expression and in expression add num(orderdate) may be it will solve your sorting problem