Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need help with Set analysis


Hi, I have a table with a couple of columns I want to roll up. There are rows by country, and sometimes the same country appears more than once.

Using set analysis, I can grab the recs just for the US, and if the sum of one of the columns is greater than the other, I subtract, if not, then its zero. I am using a formula in a variable, because it takes some math to get the results I need...

=NUM(IF(SUM({<Balance.COUNTRY={'UNITED STATES'}>}$(vGlobalEarningsCreditAllowance)) > sum({<Balance.COUNTRY={'UNITED STATES'}>}(vAnnualizedFees)),
sum({<Balance.COUNTRY={'UNITED STATES'}>}$(vGlobalEarningsCreditAllowance))-sum({<Balance.COUNTRY={'UNITED STATES'}>}$(vAnnualizedFees)),
0), '#,###.##', '.', ',')

The problem I am having, is that I need to do this for all of the countries. I need to check if vGlobalEarningsCreditAllowance is > than vAnnualizedFees at each country level, subtract if so, or set to 0 otherwise, then add them all up to get a positive number. The problem is, I cant figure out how to do this without explicitly mentioning every Country in the table and adding them all up. Is there a way to do this through set analysis or aggregating?

I can't just do the sum of vGlobalEarningsCreditAllowance - vAnnualizedFees, because that will just sum up both columns and include the individual negative numbers, when I need those to be 0.

Thanks!

5 Replies
rustyfishbones
Master II
Master II

Can you share the App?

jerem1234
Specialist II
Specialist II

Try something like:

NUM(SUM(AGGR(IF(SUM($(vGlobalEarningsCreditAllowance)) >sum((vAnnualizedFees)),
sum($(vGlobalEarningsCreditAllowance))-sum($(vAnnualizedFees)),
0), Balance.COUNTRY)), '#,###.##', '.', ',')


Hope this helps!

Not applicable
Author

This returns the same result as if I just sum them all, so it includes the negative numbers, but i will try an play with it.

Not applicable
Author

Yeah I can't figure this out...

jerem1234
Specialist II
Specialist II

Make a straight table with a dimension of Country and then the formula:

NUM(IF(SUM($(vGlobalEarningsCreditAllowance)) >sum((vAnnualizedFees)),
sum($(vGlobalEarningsCreditAllowance))-sum($(vAnnualizedFees)),
0), '#,###.##', '.', ',')

and then see if the numbers for each country are coming back right.

Also what are the definitions of your two variables?