How to handle null dates in calculated dimensions

[#1]

Dear QV Experts,

A Combo chart with one dimension as 'Client' &

                                      Expression as  [Claim amount /  (Period Days / (Date Diff* Earned Premium))

With this dimension & Expression, it is displaying correctly.

When i introduce one more calculated dimension, then the value of expression reduces.

After analysis identified that the Earned Premium should not be based on Claim Open Date because Earned Premium is calculated whether they Claimed or not.

The Calculated dimension is,

Dimension

=IF(DateModeLatest='Quarterly',[CLM_IN_QTR],IF(DateModeLatest='Monthly',[CLM_IN_MONTH],WD_CLAIMDIM_CLAIMFACT.CLM_IN_YEAR))

CLM_IN_QTR is calculated based on Claim open date & the script below,

Right((ApplyMap('QUARTER_DETAILS',Month(Date(WD_CLAIM.CLAIM_OPEN_DT, 'M/D/YYYY')))),1)&'Q'&
Right(IF(Month(WD_CLAIM.CLAIM_OPEN_DT)=12,Year(WD_CLAIM.CLAIM_OPEN_DT)+1,
Year(WD_CLAIM.CLAIM_OPEN_DT)),2) as [CLM_IN_QTR]

Basically it filters only the records where the Claim Open Date & calculates the Earned Premium.

Can anyone please suggest me, if Claim Open Date is null then i need to update to the Start date of the retrieval.

For Ex: 1Q16 means 01st Dec,2015 to 29th Feb 2016. In that case Claim Open Date should be 01st Dec 2015.

Please suggest me.

Thanks,

Sasi

[perumal_41]

May be Suppress when Value is Null  enabled  for calculated dimension .

if Suppress when Value is Null  enabled ,then un check  like below for calculated dimension

[#3]

Thanks Perumal. But Not that Approach. I tried it already.

Tried also

1). NullValue & NullAsValue.

2). Mapping Load & Map using.

Still not working.

Thanks,

Sasi

[sunny_talwar]

I am not sure I understand the issue, would you be able to share a sample?

[#5]

Hi Sunny,

We have 11060 rows for three months.

Here the earned Premium is calculated for the Period of 90 Days [One quarter] for the Certificates they received

for a particular product from one Client.

When we have Client as one dimension then the value displays correctly.

When i add one more calculated dimension,

Dimension

=IF(DateModeLatest='Quarterly',[CLM_IN_QTR],IF(DateModeLatest='Monthly',[CLM_IN_MONTH],WD_CLAIMDIM_CLAIMFACT.CLM_IN_YEAR))

Here CLM_IN_QTR is calculated based on Claim Open Date.

CLM_IN_QTR is calculated based on Claim open date & the script below,

Right((ApplyMap('QUARTER_DETAILS',Month(Date(WD_CLAIM.CLAIM_OPEN_DT, 'M/D/YYYY')))),1)&'Q'&
Right(IF(Month(WD_CLAIM.CLAIM_OPEN_DT)=12,Year(WD_CLAIM.CLAIM_OPEN_DT)+1,
Year(WD_CLAIM.CLAIM_OPEN_DT)),2) as [CLM_IN_QTR]

Out of 11060 rows, we have 1/3 around 4000 rows having null values so it is not considered.

But the earned premium is not based on the Claim Open Date.

Earned Premium is based on Warranty Start Date [Purchase Date].

But one challenge here is we have to calculate Earned Premium only for the Quarter.

It means if the Warranty starts much before still the date should be updated as the Retrieval start date.

(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)))))

Shall i add the same in the Calculated dimension? Please suggest me.

Thanks,

Sasi