Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

AGGR Function

Hi, QlikView community.

I have a problem that I have been trying to fix but can't find the solution.

What I am trying to do is make a ABC Groups for my company list. I want to use my last year Revenue (called Income in the dataset) to set my companies to ABC analysis. I have that data below. I have companies names and income for 2016 and 2017. I want to use the income last year to make the rules for what companies are in what groups

  

CompanyYearIncome
Seafood20161.532.545,78
Cars Inc.2016374.290,77
Machinery Inc.2016108.202,71
Frozen Seafood201648.767,02
Fresh Food201648.274,13
Frozen Food Inc20160,00
Seafood201753.537
Cars Inc.20172.357
Machinery Inc.20173.198
Frozen Seafood20171.000
Fresh Food20176.225
Frozen Food Inc2017454
Candy Inc.20172

So I used this formula below in the calculated dimension. I want to make in the calculated dimension not in script. The problem I face is the company Candy Inc. That company has no income in 2016 and is not put in group

Aggr ( If((sum({1<Year ={'2016'}>}Income) / sum(TOTAL {1<Year ={'2016'}>}Income))> 0.70, 'A',

If((sum({1<Year ={'2016'}>}Income)/ sum(TOTAL {1<Year ={'2016'}>}Income))> 0.15, 'B ',

If((sum({1<Year ={'2016'}>}Income)/ sum(TOTAL {1<Year ={'2016'}>}Income))> 0.05, 'C',

'Other'))),[Company])

So what can I do to get Candy Inc in group Other even though it has no income in the last year?

Any suggestions?

1 Solution

Accepted Solutions
sunny_talwar

Try this:

= Aggr( If((sum({1<Year ={'2016'}>}Income)

/ sum(TOTAL {1<Year ={'2016'}>}Income))

> 0.70, 'A',

If((sum({1<Year ={'2016'}>}Income)

/ sum(TOTAL {1<Year ={'2016'}>}Income))

> 0.15, 'B ',

If((sum({1<Year ={'2016'}>}Income)

/ sum(TOTAL {1<Year ={'2016'}>}Income))

> 0.05, 'C',

If(Sum({1} Income) > 0, 'Other',

'Other')))),[Company])

Capture.PNG

View solution in original post

2 Replies
sunny_talwar

Try this:

= Aggr( If((sum({1<Year ={'2016'}>}Income)

/ sum(TOTAL {1<Year ={'2016'}>}Income))

> 0.70, 'A',

If((sum({1<Year ={'2016'}>}Income)

/ sum(TOTAL {1<Year ={'2016'}>}Income))

> 0.15, 'B ',

If((sum({1<Year ={'2016'}>}Income)

/ sum(TOTAL {1<Year ={'2016'}>}Income))

> 0.05, 'C',

If(Sum({1} Income) > 0, 'Other',

'Other')))),[Company])

Capture.PNG

Anonymous
Not applicable
Author

Hi Darri,

Bring null income rows also in 'others' bracket as following formula,

= Aggr( If((sum({1<Year ={'2016'}>}Income)

/ sum(TOTAL {1<Year ={'2016'}>}Income))

> 0.70, 'A',

If((sum({1<Year ={'2016'}>}Income)

/ sum(TOTAL {1<Year ={'2016'}>}Income))

> 0.15, 'B ',

If((sum({1<Year ={'2016'}>}Income)

/ sum(TOTAL {1<Year ={'2016'}>}Income))

> 0.05, 'C',

If(Sum({1} Income) > 0, 'Other',

'Other')))),[Company])

Please let us know if above is working.

Thanks,

Pooja