Column Totals

[#1]

Hello All,

Why is it that this column will not give me a total with this expression/definition attached to it? Without the 'if ( current_loan_status = 'Loan Originated',', It gives me totals and the require results. I need to narrow my data down, so I used to first if() statement to do so and now I am not getting a total calculation for this column. I realize this may not be the best way to get the results I need and I've tried more than a few ways to reword my current logic and this seemed to be the best solution until the additional if() statement was added. This is the logic I am using in my pivot table:

if ( current_loan_status = 'Loan Originated',

if( (sum(TOTAL <LoanOfficer> loan_amt)) <= 700000, sum(loan_amt*.0050),

if( (sum(TOTAL <LoanOfficer> loan_amt)) > 700000 and (sum(TOTAL <LoanOfficer> loan_amt)) <= 1100000, sum(loan_amt*.0055),

if( (sum(TOTAL <LoanOfficer> loan_amt)) > 1100000 and (sum(TOTAL <LoanOfficer> loan_amt)) <= 1500000, sum(loan_amt*.0060),

if( (sum(TOTAL <LoanOfficer> loan_amt)) > 1500000 and (sum(TOTAL <LoanOfficer> loan_amt)) <= 2000000, sum(loan_amt*.0070),

if( (sum(TOTAL <LoanOfficer> loan_amt)) > 2000000, sum(loan_amt*.0080)))))))

Thanks for the help! I am open to any suggestions or completely different direction to solve this problem.

[Gysbert_Wassenaar · Accepted solution]

Because at the column total level there are no dimension values if ( current_loan_status = 'Loan Originated', ... will always return null. Your expression cannot be evaluated at the total level. You could try wrapping it in another sum using the aggr function:

sum(aggr( if( current_loan_status  ....

                                   ....

                    .... sum(loan_amt*.0080))))))) ,DimA, DimB,....DimX))

[#3]

Perfect worked great and now I know for the future, thank you.