topic Re: Max(snapshot) by Yr-Week? in QlikView

Max(snapshot) by Yr-Week?

xarapre7 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts!

I would like to get the max (snapshots) by Yr-Wk. I was able to get the max(snapshot) for Wks 3-5 but not for Yr-Wk 2018-2 which should show a value of 2400 (1st record in the below table). I'm using this expression for the Dimesion: =if(MaxYearWkDateFlag = 1 or MonthEndDateFlag = 1, Year_Week) and then, just counting the Service ID.

Hope that you'll be able to help me with this.

Re: Max(snapshot) by Yr-Week?

ychaitanya — Fri, 20 Apr 2018 13:18:39 GMT

as per the DATA .. Your IF Condition is failing (MaxYearWkDateFlag = 1 or MonthEndDateFlag = 1

as for 2018-2 both values are NULL, this record is not coming in your data.

Please verify.

Thanks

Re: Max(snapshot) by Yr-Week?

xarapre7 — Fri, 20 Apr 2018 13:46:50 GMT

Yes, that is correct. Either of these flags should have a value for this 2018-2 record to be included in the chart and I'm not able to do that.

Re: Max(snapshot) by Yr-Week?

ychaitanya — Fri, 20 Apr 2018 13:58:59 GMT

What's the logic for deriving the Flags ?

so that we can try checking that

Thanks

Re: Max(snapshot) by Yr-Week?

xarapre7 — Fri, 20 Apr 2018 15:13:54 GMT

Chaitanya...thanks for trying to look into this.

The logic is basically to get the max snapshot date per week. So for Jan 2018, we have the following :

WK-1 for Jan 1-6

WK-2 for Jan 7-13

WK-3 for Jan14-20

WK-4 for Jan 21-27

WK-5 for Jan 28-31

The 1st flag is actually taking the max(snapshot) for that Calendar Wk (Wk-2, Wk-3, etc) and the 2nd flag is for the max(snapshot) which may fall for the last day of the month. I don't know why isn't it taking the max snapshot for WK-2 though which shouldn't be the case. Below is the script for these flags:

DATE1:

LOAD

FULL_DATE,

date(FULL_DATE,'YYYY-MM-DD') AS Calendar_Date,

FROM ...

Left join(DATE1):

LOAD

DATE(Max([Calendar Date]),'YYYY-MM-DD') as Calendar_Date,

1 as MaxYearWkDateFlag1

FROM ...

left join (DATE1)

LOAD

date#(Date(MaxMonthSnapshotDate,'YYYY-MM-DD'),'YYYY-MM-DD') as Calendar_Date,

/* NOTE: MaxMonthSnapshotDate - is previously derived from taking the max(snapshot)

1 as MaxYearWkDateFlag2

FROM ...

DATE:

LOAD

AutoNumber(FULL_DATE,'%CreateDate') as %CreateDate,

if(isnull(MaxYearWkDateFlag1),0,MaxYearWkDateFlag1) +

if(isnull(MaxYearWkDateFlag2),0,MaxYearWkDateFlag2) as MaxYearWkDateFlag

RESIDENT DATE1;

Hoping for your kind assistance.