topic Re: Sorting issue in QlikView

Sorting issue

karthikeyan1504 — Tue, 01 Mar 2016 04:35:57 GMT

Hi,

I have a cyclic group dimension for Year, Quarter & Month Year in a bar chart.

I need to show the dimensions in the calendar order such as

2014,2015,2016 for year

Q1, Q2, Q3, Q4 for Quarter

Jan-2015, Feb-2015....Dec-2015.

I need to sort all these dimensions into calendar order. I have tried to sort based on the expression of Min(Date), but it doesn't work for quarters.

Could anyone please advise me on this?

Thanks & Regards,

Karthikeyan.

jonathandienst — Tue, 01 Mar 2016 04:51:01 GMT

Create the quarters and month-year values as duals, for example, in your calendar:

...

Dual('Q' & Ceil(Month(CalDate) / 4), Ceil(Month(CalDate) / 4)) As Quarter,

Date(MonthStart(CalDate), 'MMM-yyyy') As MonthYear,

...

Now you can sort these fields numerically to get the correct order.

jonathandienst — Tue, 01 Mar 2016 04:54:06 GMT

If you need your month-year to span multiple years, you could add a month-sequence field, or define the month-year o behave like a sequence number:

...

Dual(Date(CalDate, 'MMM-yyyy'), Year(CalDate)*12 + Month(CalDate)) As MonthYear,

...

This will ensure that Jan-2016 follows after Dec-2015.

jagan — Tue, 01 Mar 2016 05:20:36 GMT

Hi,

Can you attach the script that you are using to generate this fields?

For Year and Month select Number in Sort tab & for Quarter select Text in Sort tab.

Regards,

jagan.

karthikeyan1504 — Tue, 01 Mar 2016 05:31:04 GMT

Perfect!!! It worked...

Thanks much.

mahendrankr — Wed, 09 Jun 2021 13:15:30 GMT

Awesome.. Thanks a ton