topic Calculated Dated dont show the current Month in QlikView

Calculated Dated dont show the current Month

henrybergemann — Sat, 16 Nov 2024 04:59:46 GMT

Hello Community,

i got the Problem that my Calender only shows the Field StichtagJahrMonat till 2018-11 but i need it to show me also the StichtagJahrMonat 2018-12 (so the current Month)

It has the Data till today in it in the Field: #Buchungsdatum (since Dates of today)

My Calender looks like this:

// aus den (Hardware-) Bewegungen ermitteln wir das minimale und maximale Datum für einen Stichtagskalender
tmpMinMaxDatum:
load
   min(#Buchungsdatum)   as minDatum,
   max(#Buchungsdatum)   as maxDatum
Resident
   Movements
where
   istHardware = 1
;

// auslesen von min- und max-Datum
let vMinDatum = Peek('minDatum', 0, 'tmpMinMaxDatum');
let vMaxDatum = Floor(today());

// Roh-Kalender erzeugen
tmpCalendar:
load
   *,
   floor(MonthEnd(%Date))               as tmpMonthEnd
;
load
   recno() + $(vMinDatum) - 1           as %Date
AutoGenerate $(vMaxDatum)-$(vMinDatum)+1
;

// Kalender finalisieren:
// wir behalten nur den jeweils letzten Tag eines Monats
// anreichern um weitere Felder wie Jahr und Monat
Calendar:
load
   *,
   Start & '\' & Ende                   as %StichtagStartEnde
;
load
   $(vMinDatum)                       as Start, // wir setzen unser zuvor ermitteltes minimales Datum als Startpunkt für alle Stichtag-Intervalle
   %Date,
   %Date                               as Ende, // Das jeweilige Datum markiert immer das Ende des Stichtag-Intervalls
   date(%Date)                        as Stichtag,
    year(%Date)                        as Stichtagjahr,
    month(%Date)                        as Stichtagmonat,
    year(%Date) & month(%Date)            as StichtagJahrMonat
Resident
   tmpCalendar
where
   %Date = tmpMonthEnd
;

// Intervall-Match, bei dem alle Buchungsdatumswerte in das jeweilige Sichtag-Intervall einsortiert werden (Start <= Buchungsdatum <= Ende)
tmpInterval:
IntervalMatch(%Buchungsdatum)
load
   Start,
   Ende
Resident
   Calendar
;

// aus unserer temporären Linktabelle erzeugen wir eine neue, ebenfalls temporäre Linktabelle, aber mit veränderten Schlüsseln (kein synthetischer Schlüssel aus Start und Ende mehr)
tmpLnkCalendar:
load
   %Buchungsdatum,
   Start & '\' & Ende                   as %StichtagStartEnde,
   Ende                               as #Stichtag
Resident
   tmpInterval
;

drop table tmpInterval;
drop table tmpCalendar;

I guess the Problem lays here:

floor(MonthEnd(%Date))               as tmpMonthEnd

but i dont know how to change it...

Every help is appreciated.

Have a good day
Henry

Re: Calculated Dated dont show the current Month

hopkinsc — Tue, 11 Dec 2018 13:15:32 GMT

Maybe change your autogenerate statement and use this..

AutoGenerate 1 While $(vMinDatum) + IterNo() -1 <= $(vMaxDatum);

See if that works

Re: Calculated Dated dont show the current Month

henrybergemann — Tue, 11 Dec 2018 13:22:13 GMT

Hi that didnt work, i changed

AutoGenerate $(vMaxDatum)-$(vMinDatum)+1 into your AutoGenerate 1 While $(vMinDatum) + IterNo() -1 <= $(vMaxDatum);

But i didnt get any Day now

Re: Calculated Dated dont show the current Month

hopkinsc — Tue, 11 Dec 2018 13:32:31 GMT

If this is just dates, are you able to post an example. you could hard code the min and max dates so it doesn't need to read from the data.

Re: Calculated Dated dont show the current Month

henrybergemann — Tue, 11 Dec 2018 13:46:51 GMT

So there is a lot information behind the Dates, and unfortnally it is the like 12 Years of Dates behind it, and it should automatikly expand with the Years. hopefully i understand what you have say`d

Re: Calculated Dated dont show the current Month

hopkinsc — Tue, 11 Dec 2018 15:44:11 GMT

In your script, the date field is called #Buchungsdatum but in the interval match you have it as %Buchungsdatum. is that correct?

Re: Calculated Dated dont show the current Month

hopkinsc — Tue, 11 Dec 2018 15:49:48 GMT

When you use

floor(today())

in your script and run it, what does it give you? today's date? after the reload have a look at the vMaxDatum variable and see what it says.

Re: Calculated Dated dont show the current Month

henrybergemann — Thu, 13 Dec 2018 12:44:58 GMT

Hello i changed it to :

tmpMinMaxDatum:
load
   min(#Buchungsdatum)   as minDatum,
   max(#Buchungsdatum)   as maxDatum
Resident
   Movements
where
   istHardware = 1
;

// auslesen von min- und max-Datum
let vMinDatum = Peek('minDatum', 0, 'tmpMinMaxDatum');
let vMaxDatum = Floor(today())+30;

// Roh-Kalender erzeugen
tmpCalendar:
load
   *,
   floor(MonthEnd(%Date))               as tmpMonthEnd
;
load
   recno() + $(vMinDatum) - 1           as %Date
AutoGenerate $(vMaxDatum)-$(vMinDatum)+1
;

And now it worked fine for me

Thanks for the help anyway !