topic Re: Dimention with rank and aggregation in QlikView

Dimention with rank and aggregation

Anonymous — Wed, 25 Apr 2018 14:12:42 GMT

I'm trying to build a line chart with top 5 products (by total quantity in last year), and show their sales.

My data has 5 fields:

Month

Year

Product

Sales

Quantity

X axis should be month-year. Eg: Jan-16, Feb-16….Apr.2018

Y axis should be sum of sales

Dimension should be product, but only top 5 products with most sum of quantity in previous year (2017). I think this dimension is the hardest part in the chart. I'm thinking about aggr or rank function, but not sure exactly how to do it.

Thanks

Yvonne

Re: Dimention with rank and aggregation

vishsaggi — Wed, 25 Apr 2018 14:18:07 GMT

may be this?

Dim:

= Aggr(If(Rank(Sum({< Year = {'2017'} >} Quantity)) <=5, Product), Product)

Re: Dimention with rank and aggregation

Anonymous — Wed, 25 Apr 2018 14:24:31 GMT

Great. I think it worked. A follow up question, what if I want to group all other product into "Other" in the dimension. How do I modify the expression?

Re: Dimention with rank and aggregation

vishsaggi — Wed, 25 Apr 2018 14:28:02 GMT

Can you give an example? When you club other products your rank will change so just wondering what will be your expected output?

Re: Dimention with rank and aggregation

Anonymous — Sun, 03 Jun 2018 01:23:05 GMT

Dear Vishwarath

I have the same problem and I have tried to adjust your formula to my table but i cannot get the expected result

I have a Table with a dimension "Account" and two measures in the following order "BP" (Business Plan) and "ACT" (Actual)

I selected a limitation (Fixed number) of the Top 5 accounts with the most expense and it is calculated on measure "BP" (as it is the first measure in the table).

How can i do to get the top 5 accounts calculated on measure "ACT" but without changing the order of the columns?

According to your formula I have created the following:

+aggr(If(Rank(sum(ACT))<=5,Account),Account)

Thanks in advance

Re: Dimention with rank and aggregation

vishsaggi — Sun, 03 Jun 2018 01:41:37 GMT

‌can y provide me with some sample data and your expected output I can try on?

Re: Dimention with rank and aggregation

Anonymous — Sun, 03 Jun 2018 02:08:07 GMT

Hi Vishwarath

I just get it!! My problem was I wrote the formula in Limitation option within Dimension. I had to write within the Measure

I am very happy right now and thanks for your help.

Btw the formula works without writing +aggr(). I have to study more about that topic in order to know the difference between write that expression (aggr) or not.