topic MinDate and MaxDate = Null in App Development

MinDate and MaxDate = Null

choice1848 — Wed, 16 Sep 2020 16:02:18 GMT

Hello everybody,

with the following statements I wanted to detrmine the first and the last date of the fact table (Verkausfrechnungen). The field "%Kalender" has the same format setting ('DD.MM.YY').

Temp_Calendar_Range: LOAD Num(Date#(Min(%Kalender), 'DD.MM.YY')) as MinDate, Num(Date#(Max(%Kalender), 'DD.MM.YY')) as MaxDate RESIDENT [Verkaufsrechnungen]; //--- Assign the start and end dates to variables LET vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range'); LET vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range'); DROP TABLE Temp_Calendar_Range; // Cleanup

Does anybody have an idea?

Thanks in advance.

Best regards

Choice

Re: MinDate and MaxDate = Null

marcus_sommer — Thu, 17 Sep 2020 11:13:23 GMT

If the field "%Kalender" is already a date just min/max("%Kalender") would be enough. If it's really a string you would need to convert it with date#() before applying the min/max() - but better would be to convert/interpret the field already by it's first handling.

- Marcus

Re: MinDate and MaxDate = Null

sunny_talwar — Thu, 17 Sep 2020 13:45:31 GMT

putting @marcus_sommer words into script... try one of these

Temp_Calendar_Range: LOAD Min(%Kalender) as MinDate, Max(%Kalender) as MaxDate RESIDENT [Verkaufsrechnungen]; //--- Assign the start and end dates to variables LET vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range'); LET vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range'); DROP TABLE Temp_Calendar_Range; // Cleanup

Temp_Calendar_Range: LOAD Min(Date#(%Kalender, 'DD.MM.YY')) as MinDate, Max(Date#(%Kalender, 'DD.MM.YY')) as MaxDate RESIDENT [Verkaufsrechnungen]; //--- Assign the start and end dates to variables LET vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range'); LET vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range'); DROP TABLE Temp_Calendar_Range; // Cleanup

Re: MinDate and MaxDate = Null

choice1848 — Thu, 17 Sep 2020 14:36:01 GMT

hello you two,
very well, it works. Many thanks for the fast help! Great!!!

Re: MinDate and MaxDate = Null

choice1848 — Thu, 17 Sep 2020 14:47:11 GMT

Hello again,

I've got a further question. Now I want to use the MinDate and the MaxDate as Dates formatted as "DD.MM.YYYY" in an Set Analysis expression.

How does this work?
Thanks in advance.