topic Re: Need help with Set analysis in QlikView

Need help with Set analysis

Thu, 28 Aug 2014 15:33:16 GMT

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!

Re: Need help with Set analysis

rustyfishbones — Thu, 28 Aug 2014 15:38:54 GMT

Can you share the App?

Re: Need help with Set analysis

jerem1234 — Thu, 28 Aug 2014 15:39:29 GMT

Try something like:

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

Hope this helps!

Re: Need help with Set analysis

Thu, 28 Aug 2014 15:43:19 GMT

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.

Re: Need help with Set analysis

Thu, 28 Aug 2014 16:03:05 GMT

Yeah I can't figure this out...

Re: Re: Need help with Set analysis

jerem1234 — Thu, 28 Aug 2014 16:17:08 GMT

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?