topic Re: Calendar Issues in QlikView

Calendar Issues

Wed, 25 Nov 2020 16:16:04 GMT

Dears,

I am trying to understand the concept of Calendar and dates in Qlik since I am new to it.

I have read a little and understood that using a script , I can get a master calendar with dates,Quarter, months,year.

Now I created a new file and added the script of this calendar and I have it available.

I also added a transaction table that has transactions, values, and of course a field date.

My problem is how to link those 2 tables together so that if I select a Quarter from the calendar, the filter applies also to the transactions table.

I tried with CTRL+T , I can see the 2 tables (transactions and the Calendar) but I could not find a way to "link them".

Thank you

Re: Calendar Issues

vcanale — Mon, 17 Oct 2016 16:25:25 GMT

Hi,
You could link the two tables on the "Date" field

Re: Calendar Issues

Mon, 17 Oct 2016 16:39:44 GMT

That is exactly the question...

HOW do I link the 2 tables ion the "Date" field ?

Re: Calendar Issues

MK9885 — Mon, 17 Oct 2016 16:49:26 GMT

You should have a common field in both the tables to make a link.

Usually it is DateID in both the tables.

Is it possible for you to share a screen shot of your Data Model or Script?

Also check the Date formats in both the tables.

Re: Calendar Issues

dineshm030 — Mon, 17 Oct 2016 16:51:10 GMT

Hi Pierre,

I Think, You have a lot of primary keys on both tables.

Can you please send the screenshot for me?

Regards,

Dinesh Kumar M

Re: Calendar Issues

maxgro — Mon, 17 Oct 2016 17:01:16 GMT

Transaction table and calendar table should have a date field with the same name

To rename a field in the load script

load

field as newfieldname

.....

Also check the date in the 2 tables are both number or both dates (as arvind654 suggested); you can use the table viewer (CTRL-T)

Re: Calendar Issues

MK9885 — Mon, 17 Oct 2016 17:16:32 GMT

// Date Dimension

// to load Quarters Full Name

QuarterNAME:

LOAD * Inline [

Quarter , QuarterFullName

Q1 ,FIRST

Q2 ,SECOND

Q3 ,THIRD

Q4 ,FOURTH

];

// to create Quarters ie Q1,Q2

QuartersMap:

    MAPPING LOAD

    rowno() as Month,

    'Q' & Ceil (rowno()/3) as Quarter



    AUTOGENERATE (12);



       varMinDate = num(date(mid('2016-06-01',1,10 ),'YYYY-MM-DD'));

//       varMaxDate = num(date(mid('2015-12-31',1,10 ),'YYYY-MM-DD'));

       varMaxDate = num(date(today(),'YYYY-MM-DD'));

// Creating a Temporary Calendar



TempCalendar:

    LOAD

                   $(varMinDate) + Iterno()-1 as Num,

                   Date($(varMinDate) + IterNo() - 1) as TempDate

                   AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

// Date Dimension

MasterCalendar:

LOAD*,

AutoNumber ([Year Month Num], 'PeriodID') as PeriodID,

AutoNumber (Year & Quarter, 'QuarterID') as QuarterID;

Load



trim(date(TempDate,'YYYY-MM-DD')) as DateID,

date(TempDate,'YYYY-MM-DD') as [Effective Date],

    day(TempDate) as Day,

    TempDate as [US Calendar Format],

    date(TempDate,'YYYY-MM-DD') as [UK Calendar Format],

    date(TempDate,'WWWW') as [Full Day Name],

    year(TempDate) as Year,

    inyear(TempDate,today(),0) * -1   as [CY],    // Current Year

    inyear(TempDate,today(),-1) * -1 as [First PY],

    inyear(TempDate,today(),-2) * -1 as [Second PY],

    inyeartodate(TempDate,today(),0) * -1   as [CYTD],

    inyeartodate(TempDate,today(),-1) * -1 as [First PYTD],

    inyeartodate(TempDate,today(),-2) * -1 as [Second PYTD],



    ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

    Ceil(Month(TempDate)/3) as [Quarter Number],

    quarterName(TempDate) as [Quarter Name],

    yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3)   as [Quarter Year],

    inquarter(TempDate,today(),0) * -1 as [CQ], // Current Quarter

    inquarter(TempDate,today(),-4) * -1 as [First PQ],

    inquarter(TempDate,today(),-8) * -1 as [Second PQ],

    inquartertodate(TempDate,today(),0) * -1 as [CQTD],

    inquartertodate(TempDate,today(),-4) * -1 as [First PQTD],

    inquartertodate(TempDate,today(),-8) * -1 as [Second PQTD],

    date(monthstart(TempDate),'MM') as [Month Number],

    num(month(TempDate)) as Num_Month,

    month(TempDate) as Month,

    date(monthstart(TempDate),'MMMM') as [Month Full Name],

    monthstart(TempDate) as [Calendar Month Start Date],

    monthend(TempDate) as [Calendar Month End Date],

date(monthstart(TempDate), 'MMM-YYYY') as [Month Year],

date(monthstart(TempDate), 'YYYYMM') as [Year Month Num],

    week(TempDate) as Week,

    week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Week Year],

    week(weekstart(TempDate)) & '-' & Month(TempDate)   as [Week Month],

    weekDay(TempDate) as [Week Day],



    If( TempDate > monthstart(addmonths(today(),-11)) and TempDate <= today(),1) as [Rolling 12]



Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Drop Table QuarterNAME;

Above is the Master Calendar Script.

You can see DateID field which are dates every single day in that month.

You can rename your Transaction Date field to DateID

Ex: TransactionDate as DateID,

To link Master Calendar.

Note: I've used vasMinDate starting from 2016-06-01, you can change it to whatever dates you want the calendar to start.

varMinDate = num(date(mid('2016-06-01',1,10 ),'YYYY-MM-DD'));

// varMaxDate = num(date(mid('2015-12-31',1,10 ),'YYYY-MM-DD'));

varMaxDate = num(date(today(),'YYYY-MM-DD'));

Also check YYYY-MM-DD <<< This format, is it matching with your dates format?

You can directly copy this script and just change the name of your fact date field to DateID and also check date formats. It will give you selection based on Quarters/Months/Year.

Or if there is anyone who is more expert in Master Calendar can guide you well.

Re: Calendar Issues

Anonymous — Mon, 17 Oct 2016 18:32:17 GMT

CalendarTable:

LOAD

Date,

...

;

TransactionsTable:

LOAD

date(floor(<your date field in the transactions data>)) as Date

...

;