Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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
Honored Contributor II

Re: Need help with Set analysis

Can you share the App?

jerem1234
Valued Contributor II

Re: Need help with Set analysis

Try something like:

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


Hope this helps!

Not applicable

Re: Need help with Set analysis

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

Re: Need help with Set analysis

Yeah I can't figure this out...

jerem1234
Valued Contributor II

Re: Re: Need help with Set analysis

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?