Compare my Product rank with TOP 5 Products in a Pivot table

[vidyasagar159]

Hi ALL,

I developed a Pivot table in Qlik Sense which shows the TOP 5 Ranking Products based on their yearly sales. However, my product falls under rank 200. Is there a way to add my product along with the TOP 5 ranking products?

Please find the example below for the Year 2017 :

Actual:

Rank	Product	Total Sales
1	A	$100
2	B	$80
3	C	$75

Expected:

Rank	Product	Total Sales
1	A	$100
2	B	$80
3	C	$75
200	M	$10

Thanks,

-Vidya

[ogster1974]

Use a calculated dimension instead of a dimension limit

For example

if(or(aggr(rank(sum(Sales)), Product)<=10, Product='M'), Product, null())

[vidyasagar159]

Hi Andy,

Thanks for looking into this. But I am getting an error in the below expression

=if(or(aggr(rank(sum([Total AUM])), [Product ID])<=10, WildMatch([Firm Name],'*Rowe*'), [Product ID], null())

Error in expression:  OR is not a valid function.

Thanks,

-Vidya

[vidyasagar159 · Accepted solution]

Hi,

Actually, it worked with the below expression.

=if(Aggr(rank(sum([Total AUM])),[Product ID])<10 or WildMatch([Firm Name],'*Rowe*'),Aggr(rank(sum([Total AUM])),[Product ID]))

Earlier I wrote the wrong WildMatch function in my expression that's why I wasn't getting right results.

Thanks,

-Vidya

[ogster1974]

Glad you got it working. Mark your answer correct so you can guide others with similar issue.