Two linked tables with their own date field, how to control both

[shirleyc40]

Hi!

I have an app where I have two tables, one called events and one called quotes. They each have their own date field and both tables are linked by a field called salesnumber. On one sheet, I have a date picker that controls the event date, and two bar charts.  One ;chart pulls data from the events table (call it E) and the other chart pulls data from the quote table (call it Q). E has drill down dimension of the event date week to the date and Q has a drill down of the quote data week to date.

Is it possible so that when I change the selection for event date (say I pick week 32) it also shows changes Q's dates to show week 32 and vice versa. I can't really provide an example app because I pull data from SQL.

[sunny_talwar]

Check this link @shirleyc40 

Canonical Date 

[shirleyc40]

@sunny_talwar I'm not understanding how it works exactly. 

I have data that looks like this: 

EventTable:

EventName	SystemId	EventQuoteNumber	eventDateTime
DsProWebInitialQuote	DSProWeb	24013730	9/10/2020
DsProWebInitialQuote	DSProWeb	24013731	9/10/2020
DsProWebInitialQuote	DSProWeb	8981180	8/31/2020
RatingRequest	LifeWeb	2417	8/12/2020
RatingRequest	LifeWeb	2424	8/13/2020
RatingRequest	LifeWeb	2811	8/3/2020
RatingRequest	LifeWeb	2811	8/4/2020

QuoteTable: 

QuoteId	SystemId	Status	quote_date
2417	LifeWeb	Accepted	8/13/2020
2424	LifeWeb	Accepted	8/20/2020
2811	LifeWeb	Accepted	8/9/2020

And I'm trying to have one chart that shows the date and number of distinct rating requests. 

Another chart that shows the date and the number accepted. I have these two tables linked by quoteid and eventquotenumber (renamed them to QuoteNum). I tried using this code to make the canonical date:

DateBridge:
Load
QuoteNum,
quote_date as CanonicalDate,
'Quote' as DateType
Resident QuoteInfo;

Load
QuoteNum,
eventDateTime as CanonicalDate,
'Event' as DateType
Resident TestEvents;

But when I do count({<EventName={'RatingRequest'}, DateType  = {'Event'} >} distinct EventQuoteNumber) 

 it gives a lower count