Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 Name | Unique Users | Snapshot Date |
US Weekly | 1,300,000 | 6/27/2020 |
Daily Mail | 200,000 | 6/27/2020 |
The Guardian | 4,000,000 | 6/27/2020 |
US Weekly | 1,300,000 | 6/20/2020 |
Daily Mail | 200,000 | 6/20/2020 |
The Guardian | 4,000,000 | 6/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)
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
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
With some minor tweaks to the code, this worked perfectly. Thank you so much for the suggestion!