topic Re: Sorting Month-Year by Month Name in QlikView

Sorting Month-Year by Month Name

Anonymous — Wed, 04 May 2016 14:25:02 GMT

In my chart, the data is all correct but my dimension is not sorting correctly.

=if(date(renewal_dt) <= date(GetFieldSelections(prod_dt))+182.5 and month(renewal_dt) <> month(GetFieldSelections(prod_dt)), month(renewal_dt) &' '& year(renewal_dt),

if(renewal_dt > GetFieldSelections(prod_dt), year(renewal_dt),))

This is the dimension. It displays the next six months and then after that, just the years, based on whatever production date is chosen. Attached is a snapshot of how the dimension is displayed when I sort just numerically and have 10/31/15 selected as the production date.

The years are in the correct order but the Months are not and that is what I am struggling with

Re: Sorting Month-Year by Month Name

Anonymous — Wed, 04 May 2016 14:29:00 GMT

in sort tab to sort the in expression or have the option in the other options also

Re: Sorting Month-Year by Month Name

krishnacbe — Wed, 04 May 2016 14:33:04 GMT

Hi,

Sort using expression option in the Sort Tab. Create a inline table to make the order and use it in the sort tab.

Re: Sorting Month-Year by Month Name

Anonymous — Wed, 04 May 2016 14:34:49 GMT

What would the inline table look like, if you dont mind?

Re: Sorting Month-Year by Month Name

krishnacbe — Wed, 04 May 2016 15:18:29 GMT

Inline should be like below. you need to map the month column with your month column and then sort based on MonthID

LOAD * INLINE [
MonthID, Month
1, Jan
2, Feb
3, Mar
4, Apr
5, May
6, Jun
7, Jul
8, Aug
9, Sep
10, Oct
11, Nov
12, Dec
];

Re: Sorting Month-Year by Month Name

sunny_talwar — Thu, 05 May 2016 00:54:23 GMT

Can you try this as your calculated dimension instead?

=If(Date(renewal_dt) <= Date(GetFieldSelections(prod_dt)) +182.5 and Month(renewal_dt) <> Month(GetFieldSelections(prod_dt)), MonthName(renewal_dt),

If(renewal_dt > GetFieldSelections(prod_dt),

Dual(Year(renewal_dt), Year(renewal_dt) * 100)))

Re: Sorting Month-Year by Month Name

Anonymous — Thu, 05 May 2016 12:34:53 GMT

This did not work

Re: Sorting Month-Year by Month Name

sunny_talwar — Thu, 05 May 2016 13:16:32 GMT

Would you be able to elaborate what it did? Did not work is not useful and doesn't tell me what I might have done incorrectly

Re: Sorting Month-Year by Month Name

Anonymous — Thu, 05 May 2016 13:26:26 GMT

It changed nothing, the columns rows stayed the same. I even messed with the sort and still nothing changed.

Re: Sorting Month-Year by Month Name

sunny_talwar — Thu, 05 May 2016 13:27:31 GMT

So after you used this, you did use the numerical sort order and it still did not work? Would you be able to share a sample?

Re: Sorting Month-Year by Month Name

Anonymous — Thu, 05 May 2016 13:32:38 GMT

Use Max({1} Month(

if(date(renewal_dt) <= date(GetFieldSelections(prod_dt))+182.5 and month(renewal_dt) <> month(GetFieldSelections(prod_dt)), month(renewal_dt) &' '& year(renewal_dt),

if(renewal_dt > GetFieldSelections(prod_dt), year(renewal_dt),))

) on Sort Expression.

Kind regards

Re: Sorting Month-Year by Month Name

Anonymous — Thu, 05 May 2016 13:50:27 GMT

I just noticed I still had an expression in the sort and had the box checked, this did work! Thank you so much

Re: Sorting Month-Year by Month Name

sunny_talwar — Thu, 05 May 2016 13:51:55 GMT

See a more detailed description did help us, right? I am glad we were finally able to resolve this.

Best,

Sunny