topic Re: Cumulative Sum not correct in Straight Table in QlikView

Cumulative Sum not correct in Straight Table

Wed, 25 Nov 2020 16:16:04 GMT

Dear QVExperts,

Please find the screenshot enclosed.

Expression,

EarnedPremium,

=If (MonthYear < $(vFiscalStartDt),0,

If (MonthYear > $(vFiscalEndDt),0,

Sum(Aggr((Sum({<DateType={'Premium'} , STATUS_CD = {'L'}>}Amount)) ,CERT_SQN,MonthYear,DateType))))

Claim Amt,

=If (MonthYear < $(vFiscalStartDt),0,

If (MonthYear > $(vFiscalEndDt),0,

Sum(Aggr((Sum({<DateType={'Claim'} , STATUS_CD = {'L'}>} ACTUAL_STLMNT_AM)/Count(MonthYear)),CERT_SQN, MonthYear, DateType))))

Loss Ratio,

Column(2) / Column(1)

MonthYear Earned Premium Claim Amt Loss Ratio

Dec 2012 2.20 0 0

Jan 2013 2.06 40 1.53846

Total 4.26 40 1.53846

In Loss Ratio, the highlighted one should display as 40/4.26 = 9.3897

I used all the expression Sum of Rows.

Please suggest me.

Thanks,
Sasi

Re: Cumulative Sum not correct in Straight Table

swuehl — Wed, 25 May 2016 14:15:22 GMT

Try set expressions like

DateType={'Premium'} , STATUS_CD = {'L'},

MonthYear = {"=MonthYear >=$(vFiscalStartDt) and MonthYear <=$(vFiscalEndDt)"}

DateType={'Premium'} , STATUS_CD = {'L'},

MonthYear = {">=$(=Date(vFiscalStartDt,'MMM YYYY'))<=$(=Date(vFiscalEndDt,'MMM YYYY'))"}

Re: Cumulative Sum not correct in Straight Table

miguelbraga — Wed, 25 May 2016 14:21:19 GMT

Hi Sasi Kumar,

The best practices here is to make a flag with your IF's conditions in your script, so when you use sum make only this:

EarnedPremium

Sum(Aggr((Sum({<DateType={'Premium'} , STATUS_CD = {'L'}>}Amount)) ,CERT_SQN,MonthYear,DateType))) * FlagEarnedPremium)

Claim Amt

Sum(Aggr((Sum({<DateType={'Claim'} , STATUS_CD = {'L'}>} ACTUAL_STLMNT_AM)/Count(MonthYear)),CERT_SQN, MonthYear, DateType))) * FagClaimAmt)

Loss Ratio

"Claim Amt"/ "EarnedPremium"

Regards,

Re: Cumulative Sum not correct in Straight Table

miguelbraga — Wed, 25 May 2016 14:24:47 GMT

In the Script:

If (MonthYear < $(vFiscalStartDt),0,If (MonthYear > $(vFiscalEndDt),0, 1)) as FlagEarnedPremium

If (MonthYear < $(vFiscalStartDt),0,If (MonthYear > $(vFiscalEndDt),0, 1)) as FagClaimAmt

Actually you could simplify using only one flag and repalce the flags above to FlagBetweenMonth.

Have you tried to remove DateType and CERT_SQN from your Aggr and add to the sum TOTAL?

Hope it helps

Regards,

Re: Cumulative Sum not correct in Straight Table

Wed, 25 May 2016 14:48:24 GMT

Thanks MB. But the $(vFiscalStartDt) & $(vFiscalEndDt) entered by the users.

Hope i can't use the variables in the script unless i reload the script. That's why i didnt use it.

Thanks,

Sasi

Re: Cumulative Sum not correct in Straight Table

miguelbraga — Wed, 25 May 2016 14:58:58 GMT

Consider this solution:

EarnedPremium

=Sum

(

DateType={'Premium'} ,

STATUS_CD = {'L'},

MonthYear = {">=$(=Date(vFiscalStartDt,'DD/MM/YYYY'))<=$(=Date(vFiscalEndDt,'DD/MM/YYYY'))"}

Total Amount

)

Claim Amt

=Sum

(

DateType={'Premium'} ,

STATUS_CD = {'L'},

MonthYear = {">=$(=Date(vFiscalStartDt,'DD/MM/YYYY'))<=$(=Date(vFiscalEndDt,'DD/MM/YYYY'))"}

Total ACTUAL_STLMNT_AM)/Count(MonthYear)

)

Loss Ratio

"Claim Amt"/ "EarnedPremium"

Regards,

Re: Cumulative Sum not correct in Straight Table

Wed, 25 May 2016 15:09:29 GMT

Thanks Swuehl & MB.