Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
carlcimino
Creator II
Creator II

Calendar with Start and End Date from Fact

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)

;

1 Solution

Accepted Solutions
m_woolf
Master II
Master II

CommonCalendar:

Load

     .

     .

     .

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

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

View solution in original post

4 Replies
m_woolf
Master II
Master II

CommonCalendar:

Load

     .

     .

     .

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

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

CarlosAMonroy
Creator III
Creator III

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
Creator II
Creator II
Author

It works but why a & instead of a $?

m_woolf
Master II
Master II

I mis-typed.should be $