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: 
Not applicable

Sorting quarterly dates

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.

2015-09-29_14-03-32.jpg

Regards,

Ian

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

5 Replies
marcus_sommer

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

Not applicable
Author

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

marcus_sommer

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

Not applicable
Author

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

swapneel22
Contributor II
Contributor II

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