Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
j_schmidt
Contributor

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
j_schmidt
Contributor

Re: Sorting and time dimension

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

13 Replies
rahulpawarb
Valued Contributor III

Re: Sorting and time dimension

Hello John,

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

Thank you!

Rahul

j_schmidt
Contributor

Re: Sorting and time dimension

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
Contributor

Re: Sorting and time dimension

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
Valued Contributor III

Re: Sorting and time dimension

Could you please share application with sample data?

Regards!

Rahul

vinieme12
Esteemed Contributor II

Re: Sorting and time dimension

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

Just use sortby expression Month(Datefield)

vlad_komarov
Valued Contributor II

Re: Sorting and time dimension

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


j_schmidt
Contributor

Re: Sorting and time dimension

Hi,

in which format would you like the data?

Not sure what exactly to give to you.

j_schmidt
Contributor

Re: Sorting and time dimension

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.

j_schmidt
Contributor

Re: Sorting and time dimension

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 :/