topic Re: Sorting Problem - Pivot table in QlikView

Sorting Problem - Pivot table

Mon, 27 Jul 2015 13:27:05 GMT

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,

Re: Sorting Problem - Pivot table

sunny_talwar — Mon, 27 Jul 2015 13:28:25 GMT

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)

Re: Sorting Problem - Pivot table

Gysbert_Wassenaar — Mon, 27 Jul 2015 13:30:45 GMT

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

Re: Sorting Problem - Pivot table

Tue, 28 Jul 2015 08:13:14 GMT

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,

Re: Sorting Problem - Pivot table

Tue, 28 Jul 2015 09:00:19 GMT

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

Regards,

Re: Sorting Problem - Pivot table

sunny_talwar — Tue, 28 Jul 2015 10:06:40 GMT

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)))))))))

Re: Sorting Problem - Pivot table

Tue, 28 Jul 2015 10:19:42 GMT

Thanks Sunny..This is perfect

Regards,

Re: Sorting Problem - Pivot table

sunny_talwar — Tue, 28 Jul 2015 10:20:27 GMT

No problem

Best,

Sunny

Re: Sorting Problem - Pivot table

Gysbert_Wassenaar — Wed, 29 Jul 2015 04:53:51 GMT

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.

Re: Sorting Problem - Pivot table

Wed, 29 Jul 2015 12:55:21 GMT

Thanks Gysbert

This was very helpful!

Regards,