Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

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
Highlighted
Creator II
Creator II

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

View solution in original post

4 Replies
Highlighted
Master II
Master II

Use a calculated dimension instead of a dimension limit

For example

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

Highlighted
Creator II
Creator II

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

Highlighted
Creator II
Creator II

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

View solution in original post

Highlighted
Master II
Master II

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