Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need to display the Expression Total in the display

Dear QV Experts,

I have two charts, One Straight Table, Another one Combo Chart,

In Straight Table, Total Of Expression Value is 13.11. But for 1Q13

ST_Table.jpg

Combo_Chart.jpg

The reason being there are some calculations involved other than the period also. I need to consider the cumulative sum.

THough i filter for 1Q13, it should display as 13.11 instead of 2152.07 in the Combo Chart.'

Please suggest me.

Thanks,

Sasi

2 Replies
Not applicable
Author

The Expression used in both the charts are below.

Expression:

=IF(AGGR(rank((((Sum({<[WD_CERTIFICATE.Fiscal Date]=,[WD_CERTIFICATE.Quarter Details]=,WD_CERTIFICATE.FiscalYear=,WD_CERTIFICATE.FiscalMonth=,
[WD_CLAIMDIM_CLAIMFACT.WD_CLAIM.CLAIM_OPEN_DT] = {'>=$(#vFiscalStartDate)<=$(#vFiscalEndDate)'},[WD_CLAIMDIM_CLAIMFACT.WD_CLAIM.STATUS_CD]={'$(vClaimStatus)'}>}

Aggr(Sum({<
[WD_CERTIFICATE.Fiscal Date]=,[WD_CERTIFICATE.Quarter Details]=,WD_CERTIFICATE.FiscalYear=,WD_CERTIFICATE.FiscalMonth=,
[WD_CLAIMDIM_CLAIMFACT.WD_CLAIM.CLAIM_OPEN_DT] = {'>=$(#vFiscalStartDate)<=$(#vFiscalEndDate)'},
[WD_CLAIMDIM_CLAIMFACT.WD_CLAIM.STATUS_CD]={'$(vClaimStatus)'}>}

distinct WD_CLAIMDIM_CLAIMFACT.WF_CLAIM_TRANS.ACTUAL_STLMNT_AM),WD_CLAIMDIM_CLAIMFACT.WD_CLAIM.CLAIM_ID)))
/
(SUM({$<
[WD_CERTIFICATE.Fiscal Date]=,[WD_CERTIFICATE.Quarter Details]=,WD_CERTIFICATE.FiscalYear=,WD_CERTIFICATE.FiscalMonth=,
[WD_CLAIMDIM_CLAIMFACT.WD_CLAIM.CLAIM_OPEN_DT] = {'>=$(#vFiscalStartDate)<=$(#vFiscalEndDate)'},
[WD_CLAIMDIM_CLAIMFACT.WD_CLAIM.STATUS_CD]={'$(vClaimStatus)'}>}

((IF(
[WD_CERTIFICATE.EXT_WRNTY_STR_DT]>$(vFiscalEndDate),0,(IF([WD_CERTIFICATE.EXT_WRNTY_END_DT]<$(vFiscalStartDate),0,
((((IF(
[WD_CERTIFICATE.EXT_WRNTY_END_DT]<$(vFiscalStartDate),$(vFiscalStartDate),IF([WD_CERTIFICATE.EXT_WRNTY_END_DT]>$(vFiscalEndDate),$(vFiscalEndDate),
[WD_CERTIFICATE.EXT_WRNTY_END_DT])))

-(IF(
[WD_CERTIFICATE.EXT_WRNTY_STR_DT]<$(vFiscalStartDate),$(vFiscalStartDate),IF([WD_CERTIFICATE.EXT_WRNTY_STR_DT]>$(vFiscalEndDate),$(vFiscalEndDate),
[WD_CERTIFICATE.EXT_WRNTY_STR_DT]))))
)+1)))))

/


((
[WD_CERTIFICATE.EXT_WRNTY_END_DT]-[WD_CERTIFICATE.EXT_WRNTY_STR_DT])+1)
)*
[Cert_Fact.PREMIUM_DUE_AIG_AM]
)))*100)),WD_CLIENT.CLIENT_DS)<=
$(vClientCount),WD_CLIENT.CLIENT_DS) 

Not applicable
Author

THe Formula for Expression, [The Requirement is below]

Loss Ratio = Claim amount / net premium earned (NPE)

Here the Numerator, Claim Amount displays correctly

net premium earned (NPE) = period days / date difference * Premium_due_AIG_AMount

Period days - >

if( Risk Effective Date > from date or  Risk Effective Date < to date) -- it should be 0

    risk effective date = wd_certificate.extendedwarenty start date

from date = input date

to date = input date

else

(Period to - period from)+1

Period from = if(risk effective date < from date , from date, else  if( risk effective date > to date then to date else risk effective date)

period to = if(risk expiry date < from date, from date, else if ( risk expiry date > todate , todate then risk expiration date)

risk expiry date = wd_certificate.extendedwarenty end date

from date = = input date

to date = input date

Problem is in this script, [Period Days]

(SUM({$<[WD_CERTIFICATE.Fiscal Date]=,[WD_CERTIFICATE.Quarter Details]=,WD_CERTIFICATE.FiscalYear=,WD_CERTIFICATE.FiscalMonth=,
[WD_CLAIMDIM_CLAIMFACT.WD_CLAIM.CLAIM_OPEN_DT] = {'>=$(#vFiscalStartDate)<=$(#vFiscalEndDate)'},
[WD_CLAIMDIM_CLAIMFACT.WD_CLAIM.STATUS_CD]={'$(vClaimStatus)'}>}

((IF(
[WD_CERTIFICATE.EXT_WRNTY_STR_DT]>$(vFiscalEndDate),0,(IF([WD_CERTIFICATE.EXT_WRNTY_END_DT]<$(vFiscalStartDate),0,
((((IF(
[WD_CERTIFICATE.EXT_WRNTY_END_DT]<$(vFiscalStartDate),$(vFiscalStartDate),IF([WD_CERTIFICATE.EXT_WRNTY_END_DT]>$(vFiscalEndDate),$(vFiscalEndDate),
[WD_CERTIFICATE.EXT_WRNTY_END_DT])))

-(IF(
[WD_CERTIFICATE.EXT_WRNTY_STR_DT]<$(vFiscalStartDate),$(vFiscalStartDate),IF([WD_CERTIFICATE.EXT_WRNTY_STR_DT]>$(vFiscalEndDate),$(vFiscalEndDate),
[WD_CERTIFICATE.EXT_WRNTY_STR_DT]
))))
)+1)))))

divide formula

date difference = Risk expiration date - risk effective date + 1