topic Re: Incorrect totals when I use IF statement in the expression in QlikView

Incorrect totals when I use IF statement in the expression

Anonymous — Fri, 05 Jan 2018 14:11:13 GMT

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

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

Anil_Babu_Samineni — Fri, 05 Jan 2018 14:16:57 GMT

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

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

sunny_talwar — Fri, 05 Jan 2018 14:18:36 GMT

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

Anonymous — Fri, 05 Jan 2018 14:34:07 GMT

Where shall I put the aggr in the above expressions?

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

sunny_talwar — Fri, 05 Jan 2018 14:41:32 GMT

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?

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

Anonymous — Fri, 05 Jan 2018 14:54:27 GMT

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

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

sunny_talwar — Fri, 05 Jan 2018 15:02:30 GMT

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

Anonymous — Fri, 05 Jan 2018 15:06:29 GMT

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

sunny_talwar — Fri, 05 Jan 2018 15:40:02 GMT

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

rwunderlich — Fri, 05 Jan 2018 18:22:28 GMT

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

Aggr(If,,,,

,Dims)