Calendar Issues

[#1]

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

[vcanale]

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

[#3]

That is exactly the question...

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

[MK9885]

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.

[dineshm030]

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

[maxgro]

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)

[MK9885]

// 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.

[Anonymous]

CalendarTable:

LOAD

Date,

...

;

TransactionsTable:

LOAD

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

...

;