Max(snapshot) by Yr-Week?

[xarapre7]

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. 

[ychaitanya]

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

CY

[xarapre7]

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.

[ychaitanya]

What's the logic for deriving the Flags ?

so that we can try checking that

Thanks

CY

[xarapre7]

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.