Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

Count Products that a Client is a top 5 Revenue generator in

Hi,

I have a flat table with 3 fields: Client, Product, Revenue, that looks like this.

ClientProductRevenue
AX$10
AY$15
AZ$8
BX$9
BY$12
CX$9
CY$5
CW$3

I need to have find a formula that return the number of products that client A is a top 5 revenue generators in. For example, if A is rank #2 in X, #5 in Y and #7 in Z in term of revenue, the formula will return "2", as there are 2 products in which A is a top 5 revenue generator.

I manage to use to create a table that tell you the revenue rank in each of the Product for client A:

- Current Selection: Client A

- Dimension: Product

- Measure: aggr(rank(sum(Revenue), 4, 1), Product, Client)

We can easily read off the table to see how many products in which A is a top 5. But is there a way to put it into a formula

Thanks,

Tong

1 Solution

Accepted Solutions
Highlighted

Re: Count Products that a Client is a top 5 Revenue generator in

May be this:

Sum(Aggr(If(Rank(Sum(Revenue), 4, 1) < 6, 1, 0), Product, Client))

View solution in original post

4 Replies
Highlighted

Re: Count Products that a Client is a top 5 Revenue generator in

May be this:

Sum(Aggr(If(Rank(Sum(Revenue), 4, 1) < 6, 1, 0), Product, Client))

View solution in original post

Highlighted
MVP
MVP

Re: Count Products that a Client is a top 5 Revenue generator in

maybe

sum(TOTAL <Client> if(aggr(rank(sum(Revenue), 4, 1), Product, Client) <= 5, 1))

Highlighted
Not applicable

Re: Count Products that a Client is a top 5 Revenue generator in

Thank you! This works great. Both yours and Grossi's formula give me exactly what I need.

Highlighted
Not applicable

Re: Count Products that a Client is a top 5 Revenue generator in

Thanks! Your formula works as well. But I think Talwar's is a little more flexible as it doesn't use Total modifier. Since I'm also thinking of using the formula in a table or combo chart, Total modifier may affect the result.