Data model filter last 3 months, last 3 quarters, last 4 year-ends

[andewan]

I need my data model to filter Fact_Product QVD (containing monthly data for last 5 years) for the following SNAPSHOT_DATE values.

Last 3 months
Last 3 quarters
Last 4 year-ends

What are the ways to achieve that? I guess with variables? Or with MasterCalender?

[BrunPierre · Accepted solution]

Hi, Maybe something along these lines.

/*------Define variables for today's date and the start date (5 years ago)---------*/
LET vToday = Today();
LET vStartDate = Date(AddYears(Today(), -5));

/*------Create a temporary table with date ranges---------*/
TmpDate:
LOAD  Date('$(vStartDate) '+ IterNo() - 1) AS Date
AUTOGENERATE 1
WHILE '$(vStartDate)' + IterNo() - 1 <= '$(vToday)';

/*------Create flags---------*/
NoConcatenate
FlagTable:
LOAD
If(Date >= AddMonths('$(vToday)', -2) and Date <= '$(vToday)',  //Last3Months
If(Date >= AddMonths('$(vToday)', -8) and Ceil(Month(Date)/3) = Ceil(Month('$(vToday)')/3), //Last3Quarters
If(Year(Date) >= Year('$(vToday)') - 3 and Month(Date) = 12, Date,Null())) ) as SNAPSHOT_DATE  //Last4YearEnds

Resident TmpDate;


Fact_Product:
LOAD * FROM [Fact_Product.qvd] (qvd)
Where Exists(SNAPSHOT_DATE ) ;

/*------Drop/Filter-------*/
DROP Tables TmpDate, FlagTable;

[andewan]

Thank you, BrunPierre!

I took inpiration from your code, and ultimately used the code below to achieve the goal. I hope the readers look at both versions to pick what suits them!

It is a dummy example. 

Let vMinDate = Num(Floor('31-05-2019'));
Let vMaxDate = Num(Floor('30-06-2024'));


/*------Create a temporary table with date ranges---------*/
TempCalendar:
Load Date(MonthEnd(Date#('$(varMinDate)', 'DD-MM-YYYY'), IterNo() - 1), 'DD-MM-YYYY') AS SNAPSHOT_DATE
AUTOGENERATE 1
WHILE MonthEnd(Date#('$(varMinDate)', 'DD-MM-YYYY'), IterNo() - 1) <= Date#('$(varMaxDate)', 'DD-MM-YYYY');


MasterCalendar:
Load *,
If(IsLast3Months = 1 Or IsLast3Quarters = 1 Or IsLast4YearEnds = 1, 'Y', 'N') As Final_Date_Selection
;
LOAD 
    SNAPSHOT_DATE,
    QuarterEnd(SNAPSHOT_DATE) As QuarterEnd,
    If(SNAPSHOT_DATE >= MonthEnd(AddMonths('$(vMaxDate)', -3)), 1, 0) AS IsLast3Months,
    If(SNAPSHOT_DATE = QuarterEnd(SNAPSHOT_DATE)
    And SNAPSHOT_DATE >= QuarterStart(AddMonths('$(vMaxDate)', -9)) 
    And SNAPSHOT_DATE <= QuarterEnd('$(vMaxDate)'), 1, 0) AS IsLast3Quarters,
    If(SNAPSHOT_DATE = YearEnd(SNAPSHOT_DATE)
    And SNAPSHOT_DATE >= YearEnd(AddYears('$(vMaxDate)', -4)), 1, 0) AS IsLast4YearEnds
Resident TempCalendar  
;