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

[#1]

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

[robert99]

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

[#3]

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

[Anonymous]

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.

[#5]

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;

[robert99]

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

[robert99]

Try this

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

ActivityID)

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

[#8]

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

[robert99]

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

[#10]

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