Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a DimensionDate with dates from 1900 to 2018.
My fact contains data from 2007-01-01 to today 2013-03-12
How would you then do so i dont load all dates from my dimension (my listbox will show to many dates or years and so on)
(I could do this in SQL where i select a hardcoded date, but i rather want to peek down into my fact table )
Lets say i have this:
FactSales:
Amount
DateKey
LET vMinLoadDate = peek(FactSales,'DateKey',0)
Let vMaxLoadDate = peek(FactSales,'DateKey,1)
DimDate:
DateKey (YYYYMMDD)
Date_Clean (YYYY-MM-DD)
and so on
from xxxxx
where DateKey between '($vMinLoadDate)' and '($vMaxLoadDate)'
Am i able to do so? or how would you do it?
Nevermind i answered my own question. Sorry for that
Let vMinLoadDate = num(peek('DateKey', 0, 'FactInternetsales'));
Let vMaxLoadDate = num(peek('DateKey', -1, 'FactInternetsales'));
If your Fact table is sorted, then your solution will work. If not, you can use
MinMaxDate:
Load Min(Date) as MinDate, Max(Date) as MaxDate resident FactInternetsales;
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
Calendar:
Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;
HIC
okay - can you explain what the two function does? and why you do so?
What if i have a DateDimension AND a master calendar?
My mastercalender i use example date_Clean but datekey is the surrogate key. (YYYYMMDD)
The Min() and the Max() functions pick out the smallest and largest value. These are then assigned to the variables.
Then I create the Master Calendar/Date dimension using an Autogenerate instead of loading from a table - but both ways work fine.
If your date has the format YYYYMMDD, you need to load it using an interpretation function, otherwise none of the Date/Time-functions work. Hence:
Date#(DateKey,'YYYYMMDD') as DateKey
HIC