Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
carlcimino
Contributor

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
mwoolf
Honored Contributor II

Re: Calendar with Start and End Date from Fact

CommonCalendar:

Load

     .

     .

     .

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

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

4 Replies
mwoolf
Honored Contributor II

Re: Calendar with Start and End Date from Fact

CommonCalendar:

Load

     .

     .

     .

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

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

CarlosAMonroy
Contributor III

Re: Calendar with Start and End Date from Fact

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
Contributor

Re: Calendar with Start and End Date from Fact

It works but why a & instead of a $?

mwoolf
Honored Contributor II

Re: Calendar with Start and End Date from Fact

I mis-typed.should be $