Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
schmidtj
Creator II
Creator II

Sorting and time dimension

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?

1 Solution

Accepted Solutions
schmidtj
Creator II
Creator II
Author

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 ...

View solution in original post

13 Replies
rahulpawarb
Specialist III
Specialist III

Hello John,

You can apply custom sort present under sorting tab for month dimension.

Thank you!

Rahul

schmidtj
Creator II
Creator II
Author

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 😕

mayuresh_d
Partner - Creator
Partner - Creator

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.

rahulpawarb
Specialist III
Specialist III

Could you please share application with sample data?

Regards!

Rahul

vinieme12
Champion III
Champion III

It is because now a single month has multiple values to sort on

Just use sortby expression Month(Datefield)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vlad_komarov
Partner - Specialist III
Partner - Specialist III

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.

Screen Shot 2017-02-10 at 7.08.57 AM.png


Regards,

Vlad


schmidtj
Creator II
Creator II
Author

Hi,

in which format would you like the data?

Not sure what exactly to give to you.

schmidtj
Creator II
Creator II
Author

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.

schmidtj
Creator II
Creator II
Author

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 😕