Two linked tables with their own date field, how to control both
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 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)
