Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ramyasaiqv
Contributor II

Pivot table Sorting

Is it possible to sort the chart from maximum to minimum value based on the maximum Quarter –Year?

As seen in the attached qvf. The total average column is not sorted from maximum to minimum value.

Expected sorting order:

Q3-2016(that’s max date Data is available)

1.43

1.43

1.33

1.22

1.10

Thank you,

Ramya

1 Solution

Accepted Solutions
annatrn1
New Contributor III

Re: Pivot table Sorting

Hi Ramya,

indeed - your formula got a bit more complicated

Please find attached the new qvf file with the solution for the sorting order.

Using the formula for the 3 individual areas I summed up the values and aggregated them. With a new sum over the aggregation we can calculate the values for the maximum quarter where values are available.

I used this expression for sorting the pivot table and it seems to work as expected.

6 Replies
annatrn1
New Contributor III

Re: Pivot table Sorting

You can achieve that with sorting by formula:

In order to only sort by the max date where data is available I used a set expression like this:

{$<MXT_YYYYMM={'$(=max({$<MXT_Data={"<>0"}>}MXT_YYYYMM))'}>}

ramyasaiqv
Contributor II

Re: Pivot table Sorting

Hi Anna,

Thank you for the response, i tried that but it's not working. Thanks

Ramya

annatrn1
New Contributor III

Re: Pivot table Sorting

Hi Ramya,

have you downloaded the attached qvf file? The sorting formula should work, it's sorted in the way you descrived in your initial post.

ramyasaiqv
Contributor II

Re: Pivot table Sorting

Hi Anna,

Thank you so much i'm sorry first time  i did't see the attached qvf.  Yes, the qvf you attached works as per my initial requirement that works perfect, but unfortunately i have to change my expressions and they got bit complicated now. I tried to implement your solution using my new expressions but have no success. if you get a chance please can you help me with the attached qvf. I really appreciate your help. Thank you,

Ramya

annatrn1
New Contributor III

Re: Pivot table Sorting

Hi Ramya,

indeed - your formula got a bit more complicated

Please find attached the new qvf file with the solution for the sorting order.

Using the formula for the 3 individual areas I summed up the values and aggregated them. With a new sum over the aggregation we can calculate the values for the maximum quarter where values are available.

I used this expression for sorting the pivot table and it seems to work as expected.

ramyasaiqv
Contributor II

Re: Pivot table Sorting

Hi Anna,

It worked.Thank you so much for your time and help.

Ramya