Calendar with Start and End Date from Fact

[carlcimino]

Hello, I am trying to limit my calendar using the max and min dates from my fact dataset.  What I have done so far is create the max and min dates in a temp table and used those to set the variable.  The part I am having trouble with is how to apply those variables to the calendar itself (I am not auto generating the calendar it is a static table from our corporate systems).  Any help would be appreciated.

TempCal:

Load

min(CREATE_DATE) as Min_Create_Date,

max(CREATE_DATE) as Max_Create_Date

Resident CombinedQuotes

;

Let vMaxDate = peek(Max_Create_Date,0,'TempCal');

let vMinDate = peek(Min_Create_Date,0,'TempCal')

;

CommonCalendar:

Load

    ACCOUNTING_YEAR,

    left(ACCOUNTING_QUARTER,2) as ACCOUNTING_QUARTER,

    ACCOUNTING_MONTH,

    MONTH_NUMBER,

    DAY_OF_WEEK,

    ACCOUNTING_DATE,

    ACCOUNTING_MONTH_YEAR,

    "Date",

    CALENDAR_YEAR,

    CALENDAR_WEEK_NUMBER,

    CY_FLAG,

    PY_FLAG,

    CM_FLAG,

    FUTURE_FLAG,

    YTD_FLAG,

    PYTD_FLAG  

FROM [lib://Daily_Quote_QVDs/AcctCal.qvd](qvd)

;

[m_woolf · Accepted solution]

CommonCalendar:

Load

     .

     .

     .

FROM [lib://Daily_Quote_QVDs/AcctCal.qvd](qvd)

where Date>='&(vMinDate)' and Date <= '&(vMaxDate)' ;

[CarlosAMonroy]

Hi Carl,

You're missing the where clause. Just have to add the condition where Date greater than your MIN variable and less than your MAX variable.

Thanks,

Carlos

[carlcimino]

It works but why a & instead of a $?

[m_woolf]

I mis-typed.should be $