Pivot table Sorting

[ramyasaiqv]

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

[annatrn1]

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]

Hi Anna,

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

Ramya

[annatrn1]

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]

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 · Accepted solution]

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]

Hi Anna,

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

Ramya