21 Replies Latest reply: Jun 30, 2016 2:23 AM by Deepak Sharma

# Displaying Top n Values in Pivot table

Hi

I am trying to display top n values (5,10,15...etc) in Pivot table. As Dimension Limits is not available in pivot table someone assist me to achive this functionality.

RegionCountryStateRisk CategoryRisk Score
ASPINDUPAccounting5
ASPSrilunkaJFCivil80
EURUKLNEU20
EURUKLNCredit20
EURGER-RR39
NAUSILWR95
NAUSFLRR5

In expression tab I am calculating Avg(Risk Score)

• ###### Re: Displaying Top n Values in Pivot table

Have you tried like this may be:

If(Rank(TOTAL Avg([Risk Score])) <= YourVariable, Avg([Risk Score]))

Here you will use YourVariable to decide what top value to include (5, 10, 15....)

• ###### Re: Displaying Top n Values in Pivot table

Hi Sunny

I addet this expression for top 2 values ...I am getting 2 values but rest of the values are coming as blank "-" I want to filter out such values.

Am I missing some thing or doing incorrectly.

• ###### Re: Displaying Top n Values in Pivot table

Use the same if statement for all your expressions

If(Rank(TOTAL Avg([Risk Score])) <= YourVariable, Expression1)

If(Rank(TOTAL Avg([Risk Score])) <= YourVariable, Expression2)

...

• ###### Re: Displaying Top n Values in Pivot table

Hi Sunny

I have only one expression.

Regards

• ###### Re: Displaying Top n Values in Pivot table

PFA Solution

Vikas

• ###### Re: Displaying Top n Values in Pivot table

Hi Vikas

Output is not correct as per the table top 2 values are 95 and 80....but its showing 5 and 95

• ###### Re: Displaying Top n Values in Pivot table

Modifing Vikas's solution to use the expression I suggested

If(Rank(TOTAL Avg([Risk Score])) <= vMax, Avg([Risk Score]))

• ###### Re: Displaying Top n Values in Pivot table

Oh your answer and my is same , but  Sunny is a Charm !!!

Vikas

• ###### Re: Displaying Top n Values in Pivot table

sorry for pfa new.

Vikas

• ###### Re: Displaying Top n Values in Pivot table

I changed the logic a bit and its working fine now

if( aggr(rank(TOTAL Avg([Risk Score])),Region,Country,[Risk Category],State)<= vMax, Avg([Risk Score]))

But when I changed the properties to Pivot table its not showing any thing.  :-(

• ###### Re: Displaying Top n Values in Pivot table

This is expected o/p ?  What you are getting ?

Vikas

• ###### Re: Displaying Top n Values in Pivot table

Hi Vikas

When I changed the vMax from 2 to 10 in your solution ... It should show all the 8 records. but it was showing 6 records.

2nd when I changed the chart proprtties to Pivot It doesnt show any value.

Sorry uploading any file is not permitted in my office, else I could have upload my qvw

• ###### Re: Displaying Top n Values in Pivot table

I have no idea what is the issue here using the expression I suggested?

• ###### Re: Displaying Top n Values in Pivot table

Hi Sunny

Expression suggested by you is giving correct result.

But here comes the problem

when I entered vMax = 2 it shows 2  records and for rest is showing Blank "-". I want to filter out those blank records.

• ###### Re: Displaying Top n Values in Pivot table

That is what is confusing me... I don't see any '-' rows here

When you open the attached qvw, do you see '-'? Can you add a screenshot to show the problem?

• ###### Re: Displaying Top n Values in Pivot table

Hi sunny

Please find the screen shot. It's the screen shot of you qvw only.

• ###### Re: Displaying Top n Values in Pivot table

Is this what you see when you open the file I have attached? Could be a version issue, I am not sure why it is showing null rows.

• ###### Re: Displaying Top n Values in Pivot table

I am using QV11 and also checked the weather I am opening the correct qvw.

but its the same result i am getting.

• ###### Re: Displaying Top n Values in Pivot table

Would you be able to share your qvw to see what the problem is?

Preparing examples for Upload - Reduction and Data Scrambling

• ###### Re: Displaying Top n Values in Pivot table

Sorry uploading any file is not permitted in my office, else I could have upload my qvw