How do sum over the top 5 products for each customer with a filter?

[hjesch]

I have a table with four columns: Customer, Product, Revenue, Date. I want to create a table with Customer as dimension, and the sum over the top 5 Product of Revenue, filtered on particular dates. I have tried to do this in set analysis:

SUM({<Product={"=RANK(SUM({<Date={somedate}>} Revenue))<=5"}>} Revenue),

but I think Qlik evaluates the Product-filter independent of the chart dimension, so it will only sum over the top 5 products overall, not per customer. How do I get the sum over the top 5 products for each customer?

[PedroNetto · Accepted solution]

Hi @hjesch !

Try: AGGR(sum(Aggr(If(Rank( Sum( Revenue)) <= 5,Sum( Revenue)) , Costumer, Product)),Costumer)