10 Replies Latest reply: May 27, 2016 7:35 AM by Robert Hutchings RSS

    Master Calendar using 3 datefields in 2 seperate tables without a common key

    Monica Chhabra

      Hi,

      I am trying to create a master calendar for the 3 datefields - 2 of which are now in same table linked by uniquekey (OppID) but 3rd datefield is in a seperate table and does not have OppID.  There is no logic to connect the 2 tables.  I want to be able to create a common calendar for the 3 datefields so that I can use the year and month in the common calendar as filters in a report in qlik sense.  I have visualizations for the 3 datefields seperately but want to be able to display the charts for a time frame for them using one filter only. I am new to Qlik sense so any help will be greatly appreciated. 

       

      Thanks,

      Monica

        • Re: Master Calendar using 3 datefields in 2 seperate tables without a common key
          Robert Hutchings

          You could use three separate calendars for every date (the easiest way)

           

          Or one floating calendar (Island Calendar)

          Set analysis - Date island

           

          or a combination of a canonical date for the two dates on the same table and use this calendar as a Island calendar for the third table

           

          Canonical Date

           

          Also this

          date question re {$<CallInDay =  VisitDay  ,  VisitDay = ,

          • Re: Master Calendar using 3 datefields in 2 seperate tables without a common key
            Monica Chhabra

            Thanks so much for your reply.  I created a canonical date for the two dates on the same table but don't know and understand how to use that calendar as a Island Calendar for the third table.  Can you shed more light on it?

             

            Thank you so much

              • Re: Master Calendar using 3 datefields in 2 seperate tables without a common key
                Robert Hutchings

                Hi Monica

                 

                Have a look through this thread

                 

                date question re {$<CallInDay =  VisitDay  ,  VisitDay = ,

                 

                It's  a variation on this approach. But VisitDate etc would be replaced with whatever you have called your canonical date and calendar fields (Say if you call them Month, Year, Day etc rather than VisitDay etc. And you canonicaldate is called DateCanonical)

                 

                then this should (might) work

                 

                sum ({$<DateUnlinkedTable =  p(DateCanonical)  ,  Day = , Month = , Year =  >}
                Revenue)

                 

                If you filter by a selection Year the DateUnlinkedTable date should be filtered by the selected year

                 

                NB. you will need to set up a month etc if you require a say month dimension in a table. This can be done as either this dimension in a chart

                Month (DateUnlinkedTable) or setting up as a calendar for the unlinked date  in the script

                 

                Hope this all makes sense (and is correct?). Let me know how it goes

                 

                But there are a number of ways to do this (see sbobbyraj reply) and various discussions on this topic. I find the above works well for me the few times I have needed to do this. It can become quite complex though. But now I try to have only one calendar not many if I can avoid it. Canonical date works well if it is possible to use this approach. I don't like the Island approach as much but use when I have no choice



              • Re: Master Calendar using 3 datefields in 2 seperate tables without a common key
                bobbyraj santhiogu

                Hi,

                As you said you don't have a commun key (OppID) between the two calendars and the third one, the commun key will be the date itself.

                So just make a join between your pivot table containing the dates from the first two tables and the dates of the third table so that the commun key will be the date.

                If you post the screenshot of your data model, we can make an exemple.

                  • Re: Master Calendar using 3 datefields in 2 seperate tables without a common key
                    Monica Chhabra

                    Qlik Date .png

                    I have created a table (code below) and it works perfectly fine for Close Date and FTA Completed Date that have a common field called Id.  The table Activity has a ActivityId that is not same as Id field in Opportunity table.  I want to use a MasterCalendar that is already created to be able to use as filters for charts for Opportunity and Activity.  Please help!

                     

                    DateBridge:

                    Load Id, [Close Date] as CanonicalDate, 'Close' as DateType

                      Resident Opportunity;

                    Load Id, [FTA Completed Date] as CanonicalDate, 'FTA' as DateType

                        Resident Opportunity;

                      • Re: Master Calendar using 3 datefields in 2 seperate tables without a common key
                        Robert Hutchings

                        Try this

                         

                        count ({$< ActivityDate =  p(CanonicalDate)  ,  Day = , Month = , Year =  >}

                        ActivityID)

                         

                        You need to include Quarter etc if you make a selection Quarter etc

                          • Re: Master Calendar using 3 datefields in 2 seperate tables without a common key
                            Monica Chhabra

                            Tried below 2 -

                            First: (with p)

                            Count ({$< ActivityDate =  p(CanonicalDate)  ,  Day = , Month = [Month Activity] , Year = [Fiscal Year Activity]  >} ActivityId)


                            Second: (without p)

                            Count ({$< ActivityDate =  p(CanonicalDate)  ,  Day = , Month = [Month Activity] , Year = [Fiscal Year Activity]  >} ActivityId)

                             

                            Month Activity and Fiscal Year Activity are the fields in the Activity table (screenshot above post)

                             

                            Both give a count of 4467 for year and month individually.  There are total of 4467 - please see screenshot below (bottom right bar chart that displays Activity):

                            Sure I am doing a conceptual mistake here..Further guidance will be very appreciated.  Regards

                            Qlik Date UI.png

                              • Re: Master Calendar using 3 datefields in 2 seperate tables without a common key
                                Robert Hutchings

                                what happens if you try this

                                 

                                count ({$< [Activity Date] =  P(CanonicalDate)  ,  Day = , Month = , Year =  , FiscalYear = , FiscalQuarter =  >}

                                ActivityID)

                                 

                                is there a space between Activity and date? -- > [Activity Date] or --> ActivityDate

                                 

                                But whatever you select regarding calendar dates(Month etc) must be entered as shown above

                                 

                                for eg this worked for both month and MonthFin. But it wouldn't work for Quarter say until I added Quarter

                                 

                                Sum({<InvDate = P (DateCanon) , Year = , Month =  , Day =  ,  YearFin = , MonthFin =  >}

                                InvL_Ext_Cost)

                                 

                                Try  and let me know if you need more help

                                  • Re: Master Calendar using 3 datefields in 2 seperate tables without a common key
                                    Monica Chhabra

                                    Thanks for the catch on the Activity Date it was with a space. 

                                    But I can't still figure out why do I keep getting total for all the years and if i drill down into month I get same count for all months.  I am not clear on this set analysis statement.

                                    I have CanonicalDate, FiscalYear, FiscalMonth, FiscalQuarter fields in calendar table and [Activity Date], [Fiscal Year Activity], [Month Activity] in Activity table.  I want to have a common filter of Fiscal year and Fiscal Quarter that I can use on Report to have a CanonicalDate as a common Dimension.  I can create a [Fiscal Quarter in the Activity table.  I don't know what set Analysis expression I would need to make it work.

                                    Any additional guidance will be very helpful

                                     

                                    Thanks

                                    Regards

                                      • Re: Master Calendar using 3 datefields in 2 seperate tables without a common key
                                        Robert Hutchings

                                        Hi

                                        "I want to have a common filter of Fiscal year and Fiscal Quarter that I can use on Report to have a CanonicalDate as a common Dimension."

                                         

                                        I also would like to do this. And you can if and only if you can link the tables and use a canonical date. I try to do this but at times it is not possible. As in your example above. You can not do it for the activity date (it seems)

                                         

                                        So in this situation I use a canonical date + a calendar Island approach. You can use a calendar Island for everything (rather than a canonical date)  but it has the limitation that you can NOT use these calendar fields for a visual dimension (they can only be used for making data filter selections) as you have tried to above. And it's slower and uses more memory to make filter selections in large files.

                                         

                                        So what to do in this situation. One option is to set up a calendar / the required date fields for the activity month in script and use this as a dimension --> month (Activity Date) as MonthActivity. This has the drawback that users can use this month dimension as a selection in error. So I tend to call it something like -->  month (Activity Date) as MthACTIVITYONLY and rename it when I use the dimension in a visual. So a user will see a strange selection if they use this and hopefully will remove it when they move from one sheet to another one not using activity fields.

                                         

                                        Another is just to use this type of formula in the expression field in the chart--> month ([Activity Date]) . But this still has the issue that users can click on this dimension in the visual and then it becomes a selection. So I tend to go for the script approach.

                                         

                                        There might be other ways of doing this but I have not come across a good one yet.

                                         

                                        Re the set analysis. Get it working first and then try to follow the logic. But you must have this field in the set analysis for any canonical fields selection made. So if a user filters by a FiscalQuarter selection then FiscalQuarter must be in the set analysys 

                                         

                                        count ({$< [Activity Date] =  P(CanonicalDate)  ,  Day = , Month = , Year =  , FiscalYear = , FiscalQuarter =  ,  FiscalMonth = >} ActivityID)

                                         

                                        But I'm not an expert on this. Please let me know if it works and mark replies as answered / helpful if one solves you first question.