Sorting Problem - Pivot table

[#1]

Hi friends,

I can't share my info in detail here. But would share my problem details theoretically

I have a pivot table in my QV dashboard, where i am sorting using Sort by Expression. The formula is correct and it shows in correct information in sorting manner.

The moment i make some selection, the sorting gets affected with 0 values in the top of pivot table following the sorting order.

I can explain in detail too.

Regards,

KC

[sunny_talwar]

May be add {1} to your sort expression to tell it not be impacted by any selection.

Edit: For instance if you current sort expression is this: Sum({<Year = {2014}>})Sales) then change it to this: Sum({1<Year = {2014}>})Sales)

[Gysbert_Wassenaar]

To make the sort expression ignore selections add {1} to the expression. For example sum(Sales) would become sum({1}Sales).

[#4]

My sort expression is like

If(Category ='A',1,

If(Category='B',2,

If(Category='C',3,

If(Category='D',4,

If(Category='E',5,

If(Category='F',6,

If(Category='G',7,

If(Category='H',8,

If(Category='I',9)))))))))

And main expression is like

=(Sum(ABC) - If(Category = 'F', sum(TOTAL {<Category = {'E'}>}ABC), 0))/1000000

Regards,

KC

[#5]

I don't understand where i am going wrong!!

Regards,

KC

[sunny_talwar · Accepted solution]

Try this:

If(Only({1} Category) ='A',1,

If(Only({1} Category)='B',2,

If(Only({1} Category)='C',3,

If(Only({1} Category)='D',4,

If(Only({1} Category)='E',5,

If(Only({1} Category)='F',6,

If(Only({1} Category)='G',7,

If(Only({1} Category)='H',8,

If(Only({1} Category)='I',9)))))))))

[#7]

Thanks Sunny..This is perfect

Regards,

KC

[sunny_talwar]

No problem

Best,

Sunny

[Gysbert_Wassenaar]

No need for so many nested if's: match(only({1}Category),'A','B','C','D','E','F','G','H','I'). But if you're basically simply sorting Category by A->Z then only({1}Category) should suffice.

[#10]

Thanks Gysbert

This was very helpful!

Regards,

KC