Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
nsnybs21qv
Contributor III
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
vishsaggi
Champion III
Champion III

may be this?

Dim:

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

View solution in original post

6 Replies
vishsaggi
Champion III
Champion III

may be this?

Dim:

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

View solution in original post

nsnybs21qv
Contributor III
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?

vishsaggi
Champion III
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?

es-amorales1
Contributor II
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)

Thanks in advance

A

vishsaggi
Champion III
Champion III

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

es-amorales1
Contributor II
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