# Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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.

Regards,

Ian

Tags (1)
1 Solution

Accepted Solutions

## Re: Sorting quarterly dates

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

4 Replies

## Re: Sorting quarterly dates

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

## Re: Sorting quarterly dates

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

[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

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

## Re: Sorting quarterly dates

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