Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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}>}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

9 Replies
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Aggr(If,,,,

,Dims)