topic Re: Sorting quarterly dates in App Development

Sorting quarterly dates

Tue, 29 Sep 2015 20:10:24 GMT

Hi,

I am presented with dates in the form of Q1 2014, Q2 2014, etc. When covering multiple years the sorting in Qlik Sense does not give me the order by year. I have read some other posts on sorting and the suggestion has been to use the Dual function but I can't find very much documentation that shows how to deal with this.

Referring to the image you can see how Q2 2015 is out of sequence. Any suggestions are very much appreciated.

Regards,

Ian

Re: Sorting quarterly dates

marcus_sommer — Wed, 30 Sep 2015 10:07:20 GMT

I'm not sure if you could use sorting-expressions within qlik sense but if and you have connected various period-fields (maybe within a master-calendar, here you find many informations about this approach: How to use - Master-Calendar and Date-Values) you could use something like avg(YourDateField). Otherwise you need to create a dual-field:

Syntax is: dual(StringValue, NumericValue) and in your case maybe this one:

dual(YourQuarterField, Year * 10 + NumericQuarter)

or if you hadn't such one

dual(YourQuarterField, Year * 10 + ceil(num(month(YourDateField)) / 3))

- Marcus

Re: Sorting quarterly dates

Wed, 30 Sep 2015 19:59:18 GMT

Hi Marcus,

Thanks you for replying so quickly. I should have clarified that my date information is formed as a single record in my Excel dump. Columns name is Quarter and each row is represented in the form of Q1 2015. When I try your example in a load script it errors out stating that Year is not found.

I'm new to scripting so most of this is trial and error for me at this stage. Here is my original script if that sheds any light on what I am attempting.

[Usage]:

LOAD [Brand],

[Quarter],

[Product],

[Machine Version],

[Ultimate],

[Industry Code],

[Country],

[SELECT Series],

[Sales Channel],

[Sales Rep],

[Unique Machines]

FROM [lib://Dashboard/Plan.xlsx]

(ooxml, embedded labels, table is Usage);

Thanks again,

Ian

Re: Sorting quarterly dates

marcus_sommer — Thu, 01 Oct 2015 01:13:21 GMT

In this case you needs to split your quarter-field with string-functions like in this example:

dual(Quarter, num(mid(Quarter, 4)) * 10 + num(mid(Quarter, 2, 1))) as Quarter

and you could generate a date,too:

makedate(num(mid(Quarter, 4)), num(mid(Quarter, 2, 1)) * 3 - 2) as Date // first date in quarter

- Marcus

Re: Sorting quarterly dates

Thu, 01 Oct 2015 20:02:00 GMT

Thank you very much for this Marcus. It worked perfectly. I can see where I can use this in similar situation where I have concatenated data provided to me.

Cheers,

Ian

Re: Sorting quarterly dates

swapneel22 — Wed, 06 May 2020 04:48:59 GMT

Thanks @marcus_sommer Your solution was handy.

Appreciate all the contributors & members of the Qlik community for sharing their knowledge.

Its a very reliable and vast forum having answers to most of the roadblocks for new developers.

Kind Regards,

Swapneel