Cumulative Sum not correct in Straight Table

[#1]

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

[swuehl · Accepted solution]

Try set expressions like

{<

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

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

>}

or

{<

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

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

>}

[miguelbraga]

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,

MB

[miguelbraga]

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,

MB

[#5]

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

[miguelbraga]

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,

MB

[#7]

Thanks Swuehl & MB.

swuehl