topic Re: An issue in calculated dimension in QlikView

An issue in calculated dimension

Wed, 25 Nov 2020 16:16:04 GMT

Dear QV Expert,

First If says 'If Warranty Start Date lesser than Retrieval Start Date (Fiscal Start Date) then Retrieval Start Date.

Second If Says 'If Warranty Start Date greater than Retrieval End Date (Fiscal End Date) Then Retrieval End Date.

Else Warranty Start Date.

The code shown below,

=IF(TRIM([WD_CERTIFICATE.EXT_WRNTY_STR_DT])<=$(#vFiscalStartDate),$(#vFiscalStartDate),

IF(TRIM([WD_CERTIFICATE.EXT_WRNTY_STR_DT])>=$(#vFiscalStartDate),$(#vFiscalEndDate),

[WD_CERTIFICATE.EXT_WRNTY_STR_DT]

))

It is working fine for single month.

But when i retrieve for two or three months, the issue is.

Here we are not considering Claim_Open_Date.

For Ex,

When we retrieve between 1st Jan to 29th Feb 2016.

The Warranty Start Date may be in Jan2016 but the Claim_Open_Date in Feb 2016 still falling in Jan 2016.

Can you please Suggest me in introducing one more IF condition so that respective Claim should allocate to respective month.

Please find enclosed the QVW, Xls & the screenshot.

Thanks,

Sasi

Re: An issue in calculated dimension

sunny_talwar — Sat, 30 Apr 2016 12:07:27 GMT

What is your expected output here?

UPDATE: Right now everything is getting evaluated to the false part of your calculated dimension here:

=If(DATE([WRNTY_STR_DT]) <= $(#vFiscalStartDate),

pick(Month(LTRIM($(#vFiscalStartDate))),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') & '-' & DATE(TRIM($(#vFiscalEndDate)),'YY'),

pick(Month(LTRIM([WRNTY_STR_DT])),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') & '-' & DATE(TRIM([WRNTY_STR_DT]),'YY'))

So I guess it would be best if we understand what exactly the goal is to help you better

Re: An issue in calculated dimension

Sat, 30 Apr 2016 12:15:01 GMT

Hi Sunny,

Oct & Nov15 lesser than Fiscal Start Date which is 1st Dec 2015.

So Sum of OCT & NOV15 to Dec15

But for Dec15, the claim open date is Jan2016, so it's value 1000 should be in Jan2016.

& Jan2016 - 100.

So the expected result should be like below

DEC-15'	160
JAN-16'	1100

Re: An issue in calculated dimension

sunny_talwar — Sat, 30 Apr 2016 12:39:00 GMT

Try this as your calculated dimension:

=If(DATE([WRNTY_STR_DT]) <= Date#('$(vFiscalStartDate)', 'MM/DD/YYYY') ,

Date(Date#('$(vFiscalStartDate)', 'MM/DD/YYYY'), 'MMM-YY'),

Date(MonthStart(CLAIM_OPEN_DT), 'MMM-YY'))

Re: An issue in calculated dimension

Sat, 30 Apr 2016 13:08:47 GMT

Thanks Sunny.

But my real time scenario is different.

WRNTY_STR_DT & CLAIM_OPEN_DT in different tables. Only CERTSQN column is in common.

When i add CLAIM_OPEN_DT in the dimension, it is pulling all the claims which is not expected.

Please suggest me.

Re: An issue in calculated dimension

sunny_talwar — Sat, 30 Apr 2016 13:10:53 GMT

Would you be able to share a sample which mimics your actual scenario. Without looking at it, it would be difficult to suggest anything

Re: An issue in calculated dimension

Sat, 30 Apr 2016 13:13:17 GMT

Thanks Sunny, Can you please share your mail-id