Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
xarapre7
Creator II
Creator II

Max(snapshot) by Yr-Week?

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.

Yr-Wk.jpg

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

4 Replies
ychaitanya
Creator III
Creator III

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
Creator II
Creator II
Author

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
Creator III
Creator III

What's the logic for deriving the Flags ?

so that we can try checking that

Thanks

CY

xarapre7
Creator II
Creator II
Author

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.