Hi all, this is driving me crazy I've tried everything that came to my mind, I combining 2 years in 1 chart using just month-day as dimension, but I cannot manage to get it sorted correctly, I'm already ordering by date field in load script and sort property in the dimension, but still, the dates not matching. Those at the end happen to be those days that were reported just in one year but not the other, any idea on how to solve this? Thanks in advance!
Dimension: Month([Report Date]) & '-' & Day([Report Date])
Sort: Expression -> [Report date]
I've also tried to create the dimension as text in load script and sort by load order but I'm getting the same result.
this would be a little tricky to explain, but I'll do my best,
I think the order in your graph is correct. The problem is the new dimension you've created. For example, let's say you pick nov 28. This date is composed by Month = November and day = 28. However, in this new dimension it will concatenate every november that exist in your data model, so included in that Nov 28, there could be included Nov 28 from 2014, from 2013 and so on.
As you correctly pointed out, the last values that appear in your graph only exist in one year but not the other. This make sense, because these are dates that only exist in the most recent year.
So what I'm trying to say is that even if for us is logical that ene - 3 should appear just before ene - 4, for QlikView is different because in the backend is still separating MonthDays from one year from Monthdays from other year.
I'm curious about the correct answer for this, I will keep trying. I hope somebody could enlighten us here.
Btw Numeric Value sort will not work because of what I've explained,
If you select just one year it would be sorted as you expect, but I think that what you want is to get the months sorted in the "normal order" (ene, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec) regardless of the selections, right?
Go to Sort Tab
Select your Calculated Dimension
Write Below in Your Expression
=Date#(Month([Report Date]) & '-' & Day([Report Date]),'MMM-D')
Now sort by Numeric Value Ascending/Descending Order