Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vidyasagar159
Contributor

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

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:

RankProductTotal Sales
1A$100
2B$80
3C$75

Expected:

RankProductTotal Sales
1A$100
2B$80
3C$75
200M$10

Thanks,

-Vidya

1 Solution

Accepted Solutions
vidyasagar159
Contributor

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

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

4 Replies
ogster1974
Honored Contributor II

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

Use a calculated dimension instead of a dimension limit

For example

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

vidyasagar159
Contributor

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

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
Contributor

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

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
Honored Contributor II

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

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