13 Replies Latest reply: Sep 19, 2017 4:22 AM by Robert Hutchings RSS

    Common calendar between 2 different tables

    Jonathan Enser

      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

        • Re: Common calendar between 2 different tables
          Jonathan Enser

          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!

          • Re: Common calendar between 2 different tables
            Jonathan Enser

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

            • Re: Common calendar between 2 different tables
              Rob Wunderlich

              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

              • Re: Common calendar between 2 different tables
                Jonathan Enser

                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.

                  • Re: Common calendar between 2 different tables
                    Jonathan Enser

                    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] ;

                    • Re: Common calendar between 2 different tables
                      Robert Hutchings

                      Hi

                       

                      "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,

                      "

                      Question 1

                      Do you mean you did this for all the required dates.

                      So you also do this for Local_day (or local_date?) and LastofCallDate. and also called them Year and Month?

                       

                      Question 2

                      Did you try what Rob suggested above?

                       

                      Because I'm confused. What you did seems different to what you seemed to ask for. A common calendar (ie one year / month / day etc applied to more than one dates) is difficult. There are no easy work around.

                       

                      You have already got a derived calendar for all your dates. this is done automatically whenever DataManager is used. They are fine but I much prefer a common calendar. This requires some effort and understanding to setup. This is all laid out in the Canonical date thread. Questions have been answered in the comments section

                        • Re: Common calendar between 2 different tables
                          Jonathan Enser

                          Thanks for responding Robert. Sounds like the Canonical date approach is what you are recommending for a common calendar.

                           

                          Interestingly, the app works if I remove the last line of the script:

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


                          The resulting data model has a synthetic key with Client, Months, and Years fields as noted below. Is this a risky setup?2017-09-18_20-24-16.jpg

                      • Re: Common calendar between 2 different tables
                        Robert Hutchings

                        Hi Jonathon

                         

                        Some don't like synthetic keys but I don't mind them in certain circumstances.

                         

                        Re using them in the way you have. I'm not a scripting expert but I wouldn't do it the way you have. But if it produces the right result (does it) so what

                         

                        Have you tried (or thought about) concatenating the three Data tables. So you would have

                        Clients (one table) >>>>BTW where did ClientTeam go to.

                        Data inc LeadStatus + Dials + Contracted Hrs (another table) with three common fields (Client / Year / Month and maybe Date)

                         

                        You would need another field to distinguish between the three tables and use this in your measures

                         

                        and finally. did you try Robs approach

                         

                        Another 2 questions.

                         

                        Do you want derived Calendars or not? (although I'm unsure why this caused you an issue)

                         

                        "Interestingly, the app works if I remove the last line of the script:

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


                        and why the change from the initial chart. You had joined tables by ClientTeam. Now its by client only



                        • Re: Common calendar between 2 different tables
                          Robert Hutchings

                          TBH I have advised clients not to play around with script before they understand what they are doing. But some still do.

                           

                          They start off with using Data Manager (this is what its for). Then they decide to convert to script without getting a good grounding first. Because they want to do something that can't be done using Data Manager.

                           

                          Sometimes it works out. If the figures are not important not an issue. But some don't check the results thoroughly. And end up with incorrect figures and use them assuming they are correct (when I was learning half my time was spend checking the figures generated by Qlik based on my poor script and then working out what was wrong with my script).