topic Re: Top 5 in pivot table not working in QlikView

Top 5 in pivot table not working

infock12 — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I looked at various forums and tried to make it work but it looks like I'm missing something.

I have a pivot table with SessionOwner and [Product Name] as Dimensions.

In expression, I have;

=if(Aggr(Rank(Count(ProductID)),SessionOwner, [Product Name])<=5, count(ProductID))

First Column - SessionOwner

Second Column - Product Name

Third Column - I am getting ALL the products but the values are shown only for the top 5 products. However, ALL the other products also appear in the result but with a blank value. How can I just display the top 5 values please?

Any help is appreciated

Re: Top 5 in pivot table not working

trdandamudi — Fri, 13 Jan 2017 14:13:24 GMT

In the Presentaion tab check "Suppress Zero-Values" and this should take care of the blank values. Hope this helps...

Re: Top 5 in pivot table not working

Anonymous — Fri, 13 Jan 2017 14:21:52 GMT

Hi Karthik,

you can try what Thirumala has said. you can also try of applying the same expression to other dimensions. But what I guess is what Thirumala suggested will work.

Re: Top 5 in pivot table not working

infock12 — Fri, 13 Jan 2017 14:27:15 GMT

Hi TD,

Thanks for this, but it is already suppressed. I think I wasn't clear in my question. It is actually not a blank value.

Let us say there are 10 products. All 10 products does have a value but they appear as '-'. Only the top 5 values have a number. I have attached an excel spreadsheet of how it appears. Hope it helps.

Re: Top 5 in pivot table not working

infock12 — Fri, 13 Jan 2017 14:36:53 GMT

Thanks AP, I have responded to Thirumala. I tried what Thirumala have suggested but the box was already checked. Basically, I want the pivot table to only show the top 5 products but it is displaying all the products. I have attached the spreadsheet in my previous response if that is helpful.

Re: Top 5 in pivot table not working

sunny_talwar — Fri, 13 Jan 2017 14:37:07 GMT

Do you have Median Sales and Median cost as your expressions also? You need to force there values to be null or 0 to hide those rows where Top 5 is 0

If(Len(Trim([Top 5])) > 0, YourExpression)

Re: Top 5 in pivot table not working

infock12 — Fri, 13 Jan 2017 14:41:25 GMT

Hi both,

Sorry I must have said this earlier. I am using two more expressions, which are Median({<Cost={'>=10'}>}Cost), Median({<Sales={'>=10'}>}Sales).

I just tried removing those two expressions from the pivot and it works correctly, so it looks like it has to do with the two additional expressions. Do I have to include this in the If(Aggr(.. statement?

Re: Top 5 in pivot table not working

sunny_talwar — Fri, 13 Jan 2017 14:43:10 GMT

Try like this

Exp1

=if(Aggr(Rank(Count(ProductID)),SessionOwner, [Product Name])<=5, count(ProductID))

Exp2

=if(Aggr(Rank(Count(ProductID)),SessionOwner, [Product Name])<=5, Median({<Cost={'>=10'}>}Cost))

Exp3

=if(Aggr(Rank(Count(ProductID)),SessionOwner, [Product Name])<=5, Median({<Sales={'>=10'}>}Sales))

Re: Top 5 in pivot table not working

infock12 — Fri, 13 Jan 2017 14:48:37 GMT

Hi Sunny,

You are absolutely right, I just responded to the original post. I have two more expressions. Do you suggest adding your expression, which is If(Len(Trim([Top 5]))>0, Median({<Cost={'>10'}>} Cost))?

Re: Top 5 in pivot table not working

sunny_talwar — Fri, 13 Jan 2017 14:50:33 GMT

You can do either or...

Use the expression Label (Top 5) to check if it is null or use the actual if statement. I would prefer expression label

Re: Top 5 in pivot table not working

infock12 — Fri, 13 Jan 2017 15:05:35 GMT

Hi Sunny,

This works perfectly, thanks for that. Just one last question. I want to sort by the first expression descending. Inside the 'Sort' option, I tried different things such as unticking all the boxes and tried sort by first expression, didn't do it. Please could you advise?

Re: Top 5 in pivot table not working

sunny_talwar — Fri, 13 Jan 2017 15:08:33 GMT

Did you try sort by y value?

Re: Top 5 in pivot table not working

infock12 — Fri, 13 Jan 2017 15:34:01 GMT

When I go into the Sort field, I tried the first Dimension (Session Owner) and selected Y value, but it didn't work. The second dimension Product Name does not give me the option of Y value

Re: Top 5 in pivot table not working

sunny_talwar — Fri, 13 Jan 2017 15:35:33 GMT

Would you be able to share a sample or a screenshot of what you have? and a little explanation of what you are looking for?

Re: Top 5 in pivot table not working

infock12 — Fri, 13 Jan 2017 15:47:24 GMT

I am trying to sort the 3rd column descending (first expression) for each SessionOwner(first column). The first two columns are dimensions.

Re: Top 5 in pivot table not working

sunny_talwar — Fri, 13 Jan 2017 15:54:16 GMT

See if this discussion offers any help

Pivot Table - Sort top article per month

Re: Top 5 in pivot table not working

infock12 — Fri, 13 Jan 2017 15:59:11 GMT

Thanks Sunny, will have a look and let you know

Re: Top 5 in pivot table not working

sunny_talwar — Fri, 13 Jan 2017 15:59:43 GMT

Sounds good

Re: Top 5 in pivot table not working

Anonymous — Fri, 13 Jan 2017 16:19:36 GMT

Hi Karthik,

Follow following steps:

Click on Properties-->Dimension--> click on suppress when value in null

In this way you will get result for top 5 values only.

Thanks,

Re: Top 5 in pivot table not working

infock12 — Fri, 13 Jan 2017 16:43:44 GMT

Thanks Pooja, very helpful. Sorted this out with Sunny's response.