Qlik Community

Qlik Sense Cloud Discussions

blutunes
New Contributor III

Common calendar between 2 different tables

Hello Experts,

I have seen some posts for this although some are older and it's not clear what the simplest approach for this is. Here's my scenario:

  • Trying to create a bar chart with year and month as the dimension and a calculated field as the measure.
  • Data sources is 2 different tables. Each table has a date field and a field used in the calculation.
    • Timesheet table containing 'hours' with a date stamp for each entry
    • Results table containing 'appointments' with a date stamp for each entry
  • Calculated field: hours per appt = hours / appointments
  • For the dimension, I can use the year/month from the Timesheet table or the Results table, not both.
    • PROBLEM: If I use the Timesheet table year and month dimension, the 'hours' for a specific month/year period is correct, but the Results table 'appointments' sum for all records, not just the specific month/year period.
  • I tried linking the date fields between the tables, but a circular reference is created (already an association by client).

Apologies if this is a repeat question. I will reference other postings if I can get some direction on the right approach.

Thanks!

Jon

13 Replies
blutunes
New Contributor III

Re: Common calendar between 2 different tables

Looks there are concepts for Canonical Dates and Master Calendar as potential approaches to link all date fields to a "common calendar" to use as a dimension and in filters. Nothing on those topics in the help menu. Am I wrong to think this is a common need? Will continue to look through community posts to figure it out. Any help finding the simplest solution is greatly appreciated!

Highlighted
blutunes
New Contributor III

Re: Common calendar between 2 different tables

I've found this video by Michael Tarrallo for Master Calendar... Understanding the Master Calendar (video)

Re: Common calendar between 2 different tables

Take a look at this tutorial. Download the Sense example by clicking the "S" icon.

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

blutunes
New Contributor III

Re: Common calendar between 2 different tables

Thanks Rob. I downloaded that qvf file and took at look... impressive work. In my case I am trying to get the dates from 3 tables to relate to the same calendar months, although the records in those tables do not relate to a single record ID (like OrderID in your case). My general goal is to sum a value in one table for a calendar month and then divide that sum by the count of records in another table for that same calendar month. The tables are related by a Team-Client field. Please see my attached data model. With this in mind, is the approach you shared still appropriate?

Thanks Rob! I really appreciate your guidance.

2017-08-30_19-14-20.jpg

Re: Common calendar between 2 different tables

I believe the suggest approach would work. The key in the DateLink table Client-Team.

-Rob

robert99
Valued Contributor II

Re: Common calendar between 2 different tables

Hi

I'm interested if this approach works or not. For this reason

"although the records in those tables do not relate to a single record ID (like OrderID in your case)."

If not this thread might help

Canonical Date

Re: Common calendar between 2 different tables

They shouldn't need to map to a single row. As long as there is a common key field (Client-Team in this example), the DateLink table will link all the many-to-many relationships. .

-Rob

blutunes
New Contributor III

Re: Common calendar between 2 different tables

I wanted to add something I learned in this effort. One thing that is probably obvious to these experts which I did not know, is that getting all tables to relate to the same month and year of a filter has to do with the Data Load Editor...

In the LOAD script for each table, I added two lines: one to assign the year of a date to 'Year' and one to assign the month of each date to 'Month'...

Original script line:

     [CallDate],

Added script lines:

    Year([CallDate]) as Year,

    Month([CallDate]) as Month,

By doing that for all table load scripts, I can then use 'Year' and 'Month' as a filter that will filter all tables in the app and also use those fields in the x-axis for charts that use data from different tables. I'm sure you experts see this as a no brainer, but it took me time to figure that out so I thought I'd share.

Thanks again for everyone that helps and contributes to this community... it's a huge help.

blutunes
New Contributor III

Re: Common calendar between 2 different tables

Ok, it seems that is not a solution. While it worked initially for the filtering and charting for single Year and Month fields, upon reloading the data (i.e., running the load script) I received the error "Duplicate Derived Value" as noted in the attached image. I thought I was so close!

Anyone have an ideas how to make this work? I also included my load script under the error message image.

2017-09-17_18-09-11.jpg

Set dataManagerTables = '','Clients','Contracted Hours','LeadStatus','Dials','Timesheets';

//This block renames script tables from non generated section which conflict with the names of managed tables

For each name in $(dataManagerTables)

    Let index = 0;

    Let currentName = name;

    Let tableNumber = TableNumber(name);

    Let matches = 0;

    Do while not IsNull(tableNumber) or (index > 0 and matches > 0)

        index = index + 1;

        currentName = name & '-' & index;

        tableNumber = TableNumber(currentName)

        matches = Match('$(currentName)', $(dataManagerTables));

    Loop

    If index > 0 then

            Rename Table '$(name)' to '$(currentName)';

    EndIf;

Next;

[Clients]:

LOAD [Client],

[Status],

[Account Manager],

[Category]

FROM [lib://AttachedFiles/Clients.xlsx]

(ooxml, embedded labels, table is Clients);

[Contracted Hours]:

LOAD [Client],

[Date Added],

[Contract Month],

    Year([Contract Month]) as Year,

    Month([Contract Month]) as Month,

[Month

(Hrs)],

[Remaining from Previous Month (Hrs)],

[Total Month (Hrs)]

FROM [lib://AttachedFiles/Clients_Contracted Hours.xlsx]

(ooxml, embedded labels, table is [Contracted Hours]);

[LeadStatus]:

LOAD

[ProjectName] AS [Client],

[Team],

[ContactsID],

[LeadStatus],

[ResultGroup],

[Company],

[Title],

[FullName],

[Business],

[Business ext.],

[Mobile],

[Address1],

[Address2],

[City],

[State],

[ZipCode],

[Email],

[Web site],

[LastOfResult],

[LastOfCallDate],

    Year([LastOfCallDate]) as Year,

    Month([LastOfCallDate]) as Month,

[LastOfCallTime],

[LastOfDuration],

[LastOfComment],

[LeadSourceName]

FROM [lib://AttachedFiles/Leads_VS.xlsx]

(ooxml, embedded labels, table is LeadStatus);

[Dials]:

LOAD

[ProjectName] AS [Client],

[Team] AS [Dials.Team],

[ContactsID] AS [Dials.ContactsID],

[CallDate],

    Year([CallDate]) as Year,

    Month([CallDate]) as Month,

[CallTime],

[Duration],

[Result],

[UpdateUserNameLastFirst],

[Comments]

FROM [lib://AttachedFiles/Dials_VS.xlsx]

(ooxml, embedded labels, table is Dials);

[Timesheets]:

LOAD

[username],

[payroll_id],

[fname],

[lname],

[number],

[group],

[local_date],

    Year([local_date]) as Year,

    Month([local_date]) as Month,

[local_day],

[local_start_time],

[local_end_time],

[tz],

[hours],

[jobcode_1] AS [Client],

[jobcode_2],

[location],

[notes],

[approved_status]

FROM [lib://AttachedFiles/Timesheets_TS.xlsx]

(ooxml, embedded labels, table is Timesheets);

[autoCalendar]:

  DECLARE FIELD DEFINITION Tagged ('$date')

FIELDS

  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),

  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),

  Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),

  Month($1) AS [Month] Tagged ('$month', '$cyclic'),

  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),

  Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),

  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),

  Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),

  Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),

  If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,

  Year(Today())-Year($1) AS [YearsAgo] ,

  If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,

  4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,

  Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,

  If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,

  12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,

  Month(Today())-Month($1) AS [MonthRelNo] ,

  If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,

  (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,

  Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [Date Added], [Contract Month], [LastOfCallDate], [CallDate], [local_date], [local_start_time], [local_end_time] USING [autoCalendar] ;

Community Browser