Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i have a sorting problem.
I have a time dimension/table drilled down to the following for the year 2000-2020: year, month, week, day, hour.
For sorting i have another field DATEID with the following format: YYYYMMDDHH.
Now i have a simple bar chart where i sum up a value over each hour of the current day.
The sorting works fine - i put a sorting using the formualr over the field DATEID.
However, if i do a bar chart where a put the months on the x-axis the sorting doesnt work anymore.
I dont understand the problem to be honest.
Can somebody help me out or give some advice?
Ok, i found 'a' solution - however i have to say that it doesnt make sense and for me it is a bug in Qlik unless
somebody can give me another explanation.
I have an integer field [MONAT_SORT] in the format YYYYMM.
Using this field for sorting doesnt work when the data is being grouped down to the hour, so (YYYYMMDDHH).
If i do another field for sorting using this function the sorting works perfectly:
DATE#([MONAT_SORT], 'YYYYMM').
Maybe somebody can enlighten me ...
Hello John,
You can apply custom sort present under sorting tab for month dimension.
Thank you!
Rahul
Hi Rahul,
yes thats what i did.
I am sorting the month dimension with the formular by DATEID (YYYYMMDDHH).
No idea why that is not working.
I think it has something to do with the fact that my fact table provides values aggregated to the hour.
The fact table and the dimension table of the time are connected.
However the sorting should still be fine, i just dont get it 😕
if you want sorting order for drill down you can select it as you want in group properties. there u find it in sort order tab.
and sorting for each field u will find it in chart properties in sort tab.
Could you please share application with sample data?
Regards!
Rahul
It is because now a single month has multiple values to sort on
Just use sortby expression Month(Datefield)
The "YYYYMMDDHH" format is not the standard one... It looks like a string value.
Try to convert it to numeric (the best option is to create another field DATEID_NUM and use it for sorting only).
You might need to use MakeDate(YYYY [ , MM [ , DD ] ]) and MakeTime(hh [ , mm [ , ss ] ]) functions (extracting corresponding values from DATEID field, and concatenate these values into DATEID_NUM field)...
Using NUM values for sorting should work fine in your case.
Regards,
Vlad
Hi,
in which format would you like the data?
Not sure what exactly to give to you.
Hi,
YYYYMMDDHH is a numeric value thats why i cant understand why it is not working.
To me this is quite buggy to be honest.
Hi,
i think too that this might be the real reason since sorting works if i display values by the hour.
Your method is unfortunately not working either - i also tried this before.
My time dimension format has a numeric value like this YYYYMMDDHH but i also have another field where i
just output to YYYYMM. Using this value for sorting doesnt work either.
I pretty frustrated since this should work ion my opinion 😕