Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
hello you two,
very well, it works. Many thanks for the fast help! Great!!!
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.