Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])