topic Re: Pivot Chart(3 dim + 1 Exp)- issue in sorting by expression in QlikView

Pivot Chart(3 dim + 1 Exp)- issue in sorting by expression

gargi_bardhan — Tue, 25 Nov 2014 12:52:36 GMT

Hi All,

This issue is really seems to be an ache

I have three dimensions - T_Date Region Currency across whcih need to do the calculation as sum(Val) .

The sorting manner required is as follows: first sort on T_Date (Descending) -> sort on sum(Val) Descending order.

Date is getting sorted but the Net value is not getting sorted in Descending order, kindly help .

I tried below options but no luck :

in sort-> t_Date -> text (Z-> A)
Region -> exprerssion -> aggr(sum(Val),T_Date) ->Descending

I am attaching the Qvw file and the source as well.

Thanks in advance.

Re: Pivot Chart(3 dim + 1 Exp)- issue in sorting by expression

Miguel_Angel_Baeyens — Thu, 04 Dec 2014 07:42:08 GMT

In Sort > Region use instead:

Sum(TOTAL <Region> Val)

That will return:

Re: Pivot Chart(3 dim + 1 Exp)- issue in sorting by expression

gargi_bardhan — Thu, 04 Dec 2014 08:03:57 GMT

Thanks Miguel for your quick response and your time,really appreciate you.

The rquirement is :-

1) the date to be sorted in Descending order - so it should be 12/20/2014 - 11/15/2014

2) Net value ->in Descending order ,here too its failing as you can see in -> 11/15/2014-> X-> Net value not getting sorted in Descending order.

I tried many ways out as well spoke to many Qlikview experts but no solution, is it not possible in Pivot to sort by Expression ?

Thanks,

Gargi

Re: Re: Pivot Chart(3 dim + 1 Exp)- issue in sorting by expression

tresB — Thu, 04 Dec 2014 08:30:24 GMT

Pivot is not sort-friendly. I have tried an alternate with Dual() in calculated dimension.

Check attached sample. Hope this helps you with an idea.

Re: Re: Pivot Chart(3 dim + 1 Exp)- issue in sorting by expression

Miguel_Angel_Baeyens — Thu, 04 Dec 2014 08:41:29 GMT

Check attached (using QlikView 11.20 SR9):

Re: Re: Re: Pivot Chart(3 dim + 1 Exp)- issue in sorting by expression

gargi_bardhan — Thu, 04 Dec 2014 10:13:29 GMT

Thanks Miguel for the solution its working fine on this ,but breaking on more than 2 dates , for more simpler explaination I have changed to year-Month . When it is collapsed it should be sorted on Dim- Year Month(Descending) ,but when Yesr-Month Dim is expanded the sorting should be done on expression(Descending)which is not happening.

The date is not getting sorted in descending order.

Hi Tresco ,

Thanks to you for your solution but it too breaking at the same ,more than two dates its failing. So can you look around with more set of records.

Attaching the updated Qvw file with more records.

Thanks,

Gargi