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: 
nbuchholz1
Contributor II
Contributor II

Identify Last Saturday of Every Quarter in QlikSense

Hi All - I have a time series data set that provides a snapshot of the unique subscribers that week. This snapshot date is Saturday every week. 

I am trying to figure out how to use either variables or a master calendar to pick the last saturday of each quarter so I can see what the unique subscriber is at the end of each quarter.  For example, I'd only want the data for the snapshot date 6/27/2020 as seen below. 

Publication NameUnique UsersSnapshot Date
US Weekly1,300,0006/27/2020
Daily Mail200,0006/27/2020
The Guardian4,000,0006/27/2020
US Weekly1,300,0006/20/2020
Daily Mail200,0006/20/2020
The Guardian4,000,0006/20/2020


Ultimately, I want to use set analysis to show me the unique users last quarter compared to the same quarter the year prior. (I use a master calendar to calculate last quarter and last quarter ly)

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

May be a flag in the script

Table:
LOAD Date,
	 'Q' & Ceil(Month(Date)/3) as Quarter,
	 'Q' & Ceil(Month(Date)/3) & Year(Date) as QuarterYear,
	 QuarterStart(Date) as QuarterStart,
	 WeekDay(Date) as WeekDay;
LOAD Date(MakeDate(2019, 1, 1) + IterNo() - 1) as Date
AutoGenerate 1
While MakeDate(2019, 1, 1) + IterNo() - 1 <= MakeDate(2020, 12, 31);

Left Join (Table)
LOAD QuarterStart,
	 Max(Date) as Date,
	 1 as LastSaturdayOfQuarter
Resident Table
Where WeekDay = 'Sat'
Group By QuarterStart;

LastSaturdayOfQuarter will be 1 for the last Saturday of the quarter which can be used in set analysis

View solution in original post

2 Replies
sunny_talwar

May be a flag in the script

Table:
LOAD Date,
	 'Q' & Ceil(Month(Date)/3) as Quarter,
	 'Q' & Ceil(Month(Date)/3) & Year(Date) as QuarterYear,
	 QuarterStart(Date) as QuarterStart,
	 WeekDay(Date) as WeekDay;
LOAD Date(MakeDate(2019, 1, 1) + IterNo() - 1) as Date
AutoGenerate 1
While MakeDate(2019, 1, 1) + IterNo() - 1 <= MakeDate(2020, 12, 31);

Left Join (Table)
LOAD QuarterStart,
	 Max(Date) as Date,
	 1 as LastSaturdayOfQuarter
Resident Table
Where WeekDay = 'Sat'
Group By QuarterStart;

LastSaturdayOfQuarter will be 1 for the last Saturday of the quarter which can be used in set analysis

nbuchholz1
Contributor II
Contributor II
Author

With some minor tweaks to the code, this worked perfectly.  Thank you so much for the suggestion!