Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Can you share the App?
Try something like:
NUM(SUM(AGGR(IF(SUM($(vGlobalEarningsCreditAllowance)) >sum((vAnnualizedFees)),
sum($(vGlobalEarningsCreditAllowance))-sum($(vAnnualizedFees)),
0), Balance.COUNTRY)), '#,###.##', '.', ',')
Hope this helps!
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.
Yeah I can't figure this out...
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?