Create Rolling Dimensions in As of Tables

[EugeniaCastilla]

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!

[110]

I usually do all my date stuff in SQL to give rolling months  quarters etc. if a dynamic solution isn't needed ( i.e. user filters on month 9, filter grabs preceding months to return July/August/September also ) .

Something that might be worth exploring is something Ometis made over here, which creates a time-aware calendar based on an input date table -  you could use this for prior quarters / rolling 12 months etc.


https://ometistoolkit.com/tools/timeawarecalendar

[EugeniaCastilla]

Hey!

Thanks for your answer, however this doesnt work. If the user selects, let's say May 2024, the values would have to change accordingly. Meaning that the first Quarter shown in the chart would be:

QTR May 2024 (includes March, April and May) and so on. 

I have gotten this far: 

And thi setting works as long as no filters are selected.... As you can see, I have now a Master Calendar, an AsOfCalendar and an AsOfQuarter calendar... this works great without filters because i use QImporte to do the sum but when a filter is applied, this Importe is not affected... 

[Kushal_Chawda]

@EugeniaCastilla  see the below article

https://community.qlik.com/t5/Member-Articles/Rolling-N-months/ta-p/1484325