Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
darrihilmarsson
Contributor III
Contributor III

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

singhpooja
Creator III
Creator III

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