Incorrect totals when I use IF statement in the expression

[Anonymous]

Hi I am using an expression

IF(PERIOD<>PERIOD_NEWLOANS,

(
SUM({<HFS={'N'},NEW_LOANS={'Y'},NEW_LOAN_FLAG={1}>}Loan_Spread)/
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}>}SPREAD_w_LP_WEIGHT)/
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.

Please help

[Anil_Babu_Samineni]

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

[sunny_talwar]

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

[Anonymous]

Where shall I put the aggr in the above expressions?

[sunny_talwar]

Something like this

Sum(Aggr(

IF(PERIOD<>PERIOD_NEWLOANS,

(
SUM({<HFS={'N'},NEW_LOANS={'Y'},NEW_LOAN_FLAG={1}>}Loan_Spread)/
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}>}SPREAD_w_LP_WEIGHT)/
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?

[Anonymous]

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

[sunny_talwar]

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?

[Anonymous]

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

[sunny_talwar]

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

[rwunderlich]

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

Aggr(If,,,,

,Dims)