topic Re: Calendar with Start and End Date from Fact in QlikView

Calendar with Start and End Date from Fact

carlcimino — Thu, 19 Jul 2018 18:34:45 GMT

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)

;

Re: Calendar with Start and End Date from Fact

m_woolf — Thu, 19 Jul 2018 18:57:34 GMT

CommonCalendar:

Load

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

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

Re: Calendar with Start and End Date from Fact

CarlosAMonroy — Thu, 19 Jul 2018 19:34:07 GMT

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

Re: Calendar with Start and End Date from Fact

carlcimino — Thu, 19 Jul 2018 19:58:40 GMT

It works but why a & instead of a $?

Re: Calendar with Start and End Date from Fact

m_woolf — Fri, 20 Jul 2018 12:11:30 GMT

I mis-typed.should be $