9 Replies Latest reply: Jan 5, 2018 1:22 PM by Rob Wunderlich

# Incorrect totals when I use IF statement in the expression

Hi I am using an expression

IF(PERIOD<>PERIOD_NEWLOANS,

(
SUM({<HFS={'N'},NEW_LOANS={'Y'},NEW_LOAN_FLAG={1}>}AVERAGE_BALANCE)
)*
\$(=\$(v_No_of_YearDays))/Ceil(MonthEnd(Date#(max(PERIOD),'YYYYMM'))-MonthStart(Date#(max(PERIOD),'YYYYMM')))
,
(
SUM({<HFS={'N'},NEW_LOANS={'Y'},NEW_LOAN_FLAG_2={1}>}ENDING_BALANCE)
))

The individual ratios at row level are correct but somehow at the totals levels I am getting totals only of the Else part of the expression. I want to get the expression total of entire data set. I am using pivot table. I cannot use aggr here since I have at least 15 dimensions and there visibility is conditional.

• ###### Re: Incorrect totals when I use IF statement in the expression

In fact, you need this for pivot table Sum(Aggr(Measure, Your dims))

• ###### Re: Incorrect totals when I use IF statement in the expression

You can use Aggr() even if the dimensions are conditional... there is no way you can get the correct sub-total without using Aggr() in a pivot table

• ###### Re: Incorrect totals when I use IF statement in the expression

Where shall I put the aggr in the above expressions?

• ###### Re: Incorrect totals when I use IF statement in the expression

Something like this

Sum(Aggr(

IF(PERIOD<>PERIOD_NEWLOANS,

(
SUM({<HFS={'N'},NEW_LOANS={'Y'},NEW_LOAN_FLAG={1}>}AVERAGE_BALANCE)
)*  \$(=\$(v_No_of_YearDays))/Ceil(MonthEnd(Date#(max(PERIOD),'YYYYMM'))-MonthStart(Date#(max(PERIOD),'YYYYMM')))
,
(
SUM({<HFS={'N'},NEW_LOANS={'Y'},NEW_LOAN_FLAG_2={1}>}ENDING_BALANCE)
)),

Dimensions))

For the Dimensions part you may have to use some dollar sign expansions, but I won't really know until you can tell me how are the conditional dimensions conditioned?

• ###### Re: Incorrect totals when I use IF statement in the expression

Thanks Sunny,

But using the above approach will get me sum of the ratios of all the rows which will be incorrect. For example if I have

Column1    Column2    Column3 (Column1/Column2)

Total                                0.5------->Desired result

1                       2             0.5

1                       2             0.5

1                       2             0.5

• ###### Re: Incorrect totals when I use IF statement in the expression

Question

1) Is Column 1 and Column 2 dimension?

2) How is 0.5, 0.5, 0.5 = 0.5? I mean can you elaborate on this logic?

• ###### Re: Incorrect totals when I use IF statement in the expression

No Column 1 & Column 2 are Measures like in the above expression Loan_Spread/ Ending Balance

So Total of Column 1=3 and Total of Column2= 6, therefore 3/6=0.5

• ###### Re: Incorrect totals when I use IF statement in the expression

That is what it should be doing without the Aggr()... I am not sure what is the problem then?

• ###### Re: Incorrect totals when I use IF statement in the expression

I don't think you want the outside Sum().  Instead just:

Aggr(If,,,,

,Dims)