Show hourly data in bar chart

[mohan2391]

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

[sunny_talwar · Accepted solution]

May be create a new field for Sorting

(Month(orderdate)*10000) + (Day(orderdate)*100) + (Hour(orderdate)) as HourSort

[mohan2391]

WoWWWWW.. 

It worked..

Thank You!! 🙂 🙂

[jonathandienst]

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.

[mahaveerbiraj]

Hi mohan,

try with  sort by expression  and in expression add num(orderdate)    may be it will solve your sorting problem