Load data from min and max date.

[#1]

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?

[#2]

Nevermind i answered my own question. Sorry for that

Let vMinLoadDate = num(peek('DateKey', 0, 'FactInternetsales'));

Let vMaxLoadDate = num(peek('DateKey', -1, 'FactInternetsales'));

[hic]

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

[#4]

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)

[hic]

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