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!
