Hey all,
I am in need of serious help. I created an As of Calendar following the approach: https://community.qlik.com/t5/Design/The-As-Of-Table/ba-p/1466130
But it is not giving me exactly what I need.
I need to visualize in a chart the Rolling Quarters based on the selection of a filter Month-Year.
If for example the user selects Jul 2024. the chart should show:
Q-Jul-2024 (sum of May 2024, June 2024 and July 2024).
Q-Apr-2024 (sum of Febr 2024, March 2024, April 2024)
Q - Jan 2024 (sum of Nov 2023, Dic 2024, Jan 2024)
And so on, up to 12 quarters maximum.
This is the code I am working with:
MasterCalendar:
LOAD
AñoMes,
MonthRolling,
Año,
Mes,
MesNum
FROM [lib://conexion_prueba_eugenia (asclepio_e.castilla)/prueba/CalendarioIQVIA.qvd]
(qvd);
// ======== Create a list of distinct Months ========
tmpAsOfCalendar:
Load distinct AñoMes as Month
Resident [MasterCalendar] ;
// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load Month as AsOfMonth
Resident tmpAsOfCalendar ;
// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Month as AsOfMonth,
AsOfMonth as MonthRolling,
Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,
Year(AsOfMonth)-Year(Month) as YearDiff
Resident tmpAsOfCalendar
Where AsOfMonth >= Month
order by Month DESC, AsOfMonth DESC;
Drop Table tmpAsOfCalendar;
I can't for the life of me create the RollingQuarter Dimensions. I have tried to play with the IterNo() <= 3 but nothing.
Please help please!
