topic Re: Sorting not displayed for Month in QlikView

Sorting not displayed for Month

Wed, 25 Nov 2020 16:16:04 GMT

Dear QVExperts,

In a calculated dimension,

=IF(DateModeLatest='Yearly',YEARNAME(DATE(WRNTY_PUR_DT)),IF(DateModeLatest='Monthly',

//MONTHNAME(DATE(WRNTY_PUR_DT)),

Month(Date(WRNTY_PUR_DT)) & ' '& Year(Date(WRNTY_PUR_DT)),

DUAL(Pick(Match(Month(WRNTY_PUR_DT), 'Dec','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov'),

'1Q','1Q','1Q','2Q','2Q','2Q','3Q','3Q','3Q','4Q','4Q','4Q')

& If( Month(WRNTY_PUR_DT) = 12, Right(Year(WRNTY_PUR_DT),2)+1, Right(Year(WRNTY_PUR_DT),2) )

, QuarterStart( WRNTY_PUR_DT,0,12))))

When i use, MONTHNAME(DATE(WRNTY_PUR_DT)), MonthNames are not sorted. Also for January Month, only year is displaying.

When i use, Month(Date(WRNTY_PUR_DT)) & ' '& Year(Date(WRNTY_PUR_DT)), the values are not sorted.

Please find the image below.

Re: Sorting not displayed for Month

rubenmarin — Tue, 14 Jun 2016 11:57:13 GMT

Hi Sasi, those values are strings so the numeric sort won't work, to keep the numeric internal value you can use a date with a format, ie:

"MONTHNAME(DATE(WRNTY_PUR_DT))" can be translated as "DATE(WRNTY_PUR_DT, 'MMM YYYY')

Re: Sorting not displayed for Month

swuehl — Tue, 14 Jun 2016 11:57:30 GMT

Why don't you use MonthName() ?

MonthName(WRNTY_PUR_DT)

or if you like another format:

Date(Monthstart(WRNTY_PUR_DT),' YYYY-MMM' )

Both create dual values and should be easy to sort in your chart.

Re: Sorting not displayed for Month

Tue, 14 Jun 2016 12:09:11 GMT

Hi Swuehl, Ruben,

When i use MonthName & Date(MonthStart(WRNTY_PUR_DT), i am getting below.

Dec2012 shown as 1Q13

Jan2013 shown as 2013

Feb2013 is correct

Mar2013 shown as 2Q13

Apr2013 is correct.

Please suggest me.

Re: Sorting not displayed for Month

swuehl — Tue, 14 Jun 2016 12:21:43 GMT

Seems somehow that your if() statement executes different branches per date.

How is DateModeLatest related to e.g.Product Category or other relevant fields?

Try with a single function in your calculated dimension:

MonthName(WRNTY_PUR_DT)

Do you still see different formats and sorting not correctly?

Re: Sorting not displayed for Month

Tue, 14 Jun 2016 13:17:08 GMT

Hi Swuehl, Ruben,

When i try going for individual dimensions for Yearly, Quarterly & Monthly if 'Enable Conditional' All Yearwise, Quarterwise, Monthwise values are sorted properly as expected.

But my DrillDown Group is not showing the icon to further drill down.

Please suggest me.

Re: Sorting not displayed for Month

swuehl — Tue, 14 Jun 2016 13:25:29 GMT

You don't have a drill down group created for your calendar fields, do you?

Re: Sorting not displayed for Month

Tue, 14 Jun 2016 13:28:34 GMT

No Swuehl. It is only for Product Category, Client, Band ,etc [For Dimension columns]

Re: Sorting not displayed for Month

rubenmarin — Tue, 14 Jun 2016 13:30:11 GMT

As for my understanding your issue is that adding the 3 conditional dimensions (I suppose only one is enabled at a time) then your drill-down stops working.

I tried with a very simple sample and I didn't found that issue. Maybe there is some change that affected values at some level of the group? If you remove the conditional dimensions it works again?

Re: Sorting not displayed for Month

Tue, 14 Jun 2016 13:41:45 GMT

Yeah Ruben, If more than one dimensions though it is conditional, then drill-down stops working.

Re: Sorting not displayed for Month

Tue, 14 Jun 2016 13:43:29 GMT

When i try providing calculated dimension like this,

I am shown with all the values. But the month is not sorted.

=IF(DateModeLatest='Yearly',YEARNAME(DATE(WRNTY_PUR_DT)),

IF(DateModeLatest='Monthly',MONTH(DATE(WRNTY_PUR_DT,'YYYYDDMM')) & ' ' & YEAR(DATE(WRNTY_PUR_DT,'YYYYDDMM')),

DUAL(Pick(Match(Month(WRNTY_PUR_DT), 'Dec','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov'),

'1Q','1Q','1Q','2Q','2Q','2Q','3Q','3Q','3Q','4Q','4Q','4Q') & If( Month(WRNTY_PUR_DT) = 12, Right(Year(WRNTY_PUR_DT),2)+1, Right(Year(WRNTY_PUR_DT),2) )

, QuarterStart( WRNTY_PUR_DT,0,12))))

Year & Quarter are perfectly already. Only the issue with Month.

Re: Sorting not displayed for Month

swuehl — Tue, 14 Jun 2016 13:43:49 GMT

I don't think I can follow what you are describing.

And I am not sure why you use conditional enablement of your calendar dimensions at all, and how the DateModeLates fields play a role here.

Please post a small sample QVW that demonstrates your issue.

Re: Sorting not displayed for Month

rubenmarin — Tue, 14 Jun 2016 13:50:58 GMT

If only monthly is causing the issue, try applying the same logic to make it a dual value (using dual or creating a date field)

"MONTH(DATE(WRNTY_PUR_DT,'YYYYDDMM')) & ' ' & YEAR(DATE(WRNTY_PUR_DT,'YYYYDDMM'))"

Can be converted to:

Date(WRNTY_PUR_DT, 'MMM YYYY')

Re: Sorting not displayed for Month

Tue, 14 Jun 2016 13:52:12 GMT

Hi Swuehl,

We have a list box, DateModest, which has three values, Yearly, Quarterly & Monthly.

We have a drill down group which comprises Product Category, Client, Band, etc

Now in the chart, we have one calculated dimension, if DateModest List box values selected Yearly displays in Year. If selected Monthly displays in Month. If selected in Quarter, it displays in Quarter.

Now if selected Yearly, Quarterly, it displays correctly & also sorted correctly.

But when i use MonthName, then only for January, i could see year for ex; Jan 2013 means it display as 2013.

But when i use IF(DateModeLatest='Monthly',MONTH(DATE(WRNTY_PUR_DT,'YYYYDDMM')) & ' ' & YEAR(DATE(WRNTY_PUR_DT,'YYYYDDMM')), i have all the values displays correctly but not sorted.

So now the issue is the month not sorted.