topic Re: Dates On Bar Graph Not Sorting Properly in QlikView

Dates On Bar Graph Not Sorting Properly

gfisch13 — Sat, 16 Nov 2024 18:20:11 GMT

Hi Everyone - hoping to get some guidance. I have a bar chart (picture attached) that shows the number of credit card charges by Month/Year.

I create this Month/Year as a calculated dimension since I do not have this data resident in any of my tables. When I try to sort by this value I cannot get the proper calendar sorting. Is this due to the calculated dimension? Would this work better if I created both the month and year in my script and then concatenated them?

The sorting is set to text at the moment and I know that is not correct.

Appreciate your help!

George

Re: Dates On Bar Graph Not Sorting Properly

Gysbert_Wassenaar — Tue, 21 Jul 2020 17:47:20 GMT

The calculated dimension you created has only text values (because you concatenate strings) and therefore can only be sorted as text.

To fix that you should use a date field. Date fields are numbers, with a textual display format. I assume CC_TRAN_DTX is a date field. A good solution is probably to create a new field in the load script in the table that contains the CC_TRAN_DTX field: Date(MonthStart(CC_TRAN_DTX ),'MMM YYYY ') as CC_TRAN_Month
Then use that new field CC_TRAN_Month as dimension in your chart. You could also try Date(MonthStart(CC_TRAN_DTX ),'MMM YYYY ') as calculated dimension in your chart. But real fields perform better than calculated dimensions.

Re: Dates On Bar Graph Not Sorting Properly

gfisch13 — Wed, 22 Jul 2020 16:38:51 GMT

Thank you for your guidance. I always struggle with the date formats and normally confuse them with some other tools I use. This worked perfectly!