MinDate and MaxDate = Null

[choice1848]

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

[marcus_sommer · Accepted solution]

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

[sunny_talwar · Accepted solution]

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

or

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

[choice1848]

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

[choice1848]

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.