topic Re: Create Rolling Dimensions in As of Tables in App Development

Create Rolling Dimensions in As of Tables

EugeniaCastilla — Fri, 15 Nov 2024 20:52:30 GMT

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!

Re: Create Rolling Dimensions in As of Tables

110 — Tue, 10 Sep 2024 10:22:38 GMT

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

Re: Create Rolling Dimensions in As of Tables

EugeniaCastilla — Mon, 16 Sep 2024 11:11:07 GMT

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...

Re: Create Rolling Dimensions in As of Tables

Kushal_Chawda — Mon, 16 Sep 2024 12:38:28 GMT

@EugeniaCastilla see the below article

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