Show values for every nth day in the past

andreas-ar · ‎2024-03-26

Hey everyone,

I have the following script for a master calendar:

Let Datumsfeld = 'Ladedatum';
Let Datumstabelle = 'Auftragsbestand';

//********************** Kalender erstellen ****************************
Datumtmp:
Load $(Datumsfeld) as Zeitstempel
Resident $(Datumstabelle)
Order By $(Datumsfeld) 
Asc
;

Let varMinDatum = Num(Peek('Zeitstempel',0,'Datumstmp'));
Let varMaxDatum = Num(MakeDate(Year(Now()),12,31));

Datumsfeld:
Load date($(varMinDatum)+IterNo() -1) as Datumsfeld
Autogenerate (1)
While $(varMinDatum)+IterNo()-1 <= $(varMaxDatum)
;

//****************** Tabelle Kalender *********************************
Kalender:
Load Datumsfeld as $(Datumsfeld),
Date(Datumsfeld) as Datum,
        Week(Datumsfeld) as Woche,
        Year(Datumsfeld) as Jahr,
        Month(Datumsfeld) as Monat,
        Day(Datumsfeld) as Tag,
        Weekday(Datumsfeld) as Wochentag,
        Day(Datumsfeld) &' '& Weekday(Datumsfeld) as Tag2,
        'Q' & ceil(month(Datumsfeld)/3) as Quartal,
        date (Monthstart(Datumsfeld), 'MMM-YYYY') as [Eindeutiger Monat],
        MonthName(Datumsfeld) as JahrMonat,
        Weekname(Datumsfeld) as [Eindeutige Woche],
        YearToDate(Datumsfeld)*-1 as YTDLaufendesJahr,
        YearToDate(Datumsfeld,-1)*-1 as YTDLetztesJahr
Resident Datumsfeld
;

The user should be able to select a date in the app and a chart should show only the data for every nth date in the past. So let's say they pick 03/26/2024 the chart has to show the values for 03/19/2024, 03/12/2024, 03/05/2024 and so on if the user picks 7 days but they also should be able to pick 30 days as an intervall.

I already tried ValueLoop() and it shows the right dates but I can't apply a date format. Furthermore it doesn't seem to be connected to the data model, as every date shows the same value.

I also found this code in the forum:

Table:
LOAD Date(AddMonths(Today(), -1) -6 +(IterNo()*7)) as Date
AutoGenerate 1
While AddMonths(Today(), -1) -6 +(IterNo()*7) <= Today();

But while it generates the right dates I don't know how to incorporate it in my existing calendar.

Thanks in advance

marcus_sommer · ‎2024-03-27

There are several approaches possible. The simplest one which is especially useful if the view is needed only in one or a few objects and the data-set isn't too large an expression like:

if(mod([Datum], 7) = mod(today(), 7), sum([Value]))

whereby the bold 7 is then replaced by your variable/selection which defines the interval and the bold today() with your selected basis-date.

By a larger data-set the above shown mod() check might be applied with a concat() against the date within an (adhoc) variable to get a list of the dates which could be queried within a set analysis.

Such date-list might be also used as selection-parameter for any action (be careful by implementing such magic).

And of course such logic could be also created within the data-model by using a kind of The As-Of Table - Qlik Community - 1466130. Maybe with a nested load-loop like:

load *, mod(Datum, iterno()) as DatumInterval while iterno() <= 30;
load date(fieldvalue('Datum', recno())) as Datum autogenerate fieldvaluecount('Datum');

and then the selection on the DatumInterval will do the job or the value is queried within a set analysis, like:

sum({< DatumInterval = p(DatumInterval) >} [Value])

Chart

Qlik Sense

Script