# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for
Did you mean:
Contributor III

## Dimention with rank and aggregation

Hi

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

1 Solution

Accepted Solutions
Champion III

may be this?

Dim:

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

6 Replies
Champion III

may be this?

Dim:

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

Contributor III
Author

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?

Champion III

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

Contributor II

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)

A

Champion III

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

Contributor II

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.

KR

A