Canonical Date giving wrong count

[shirleyc40]

I have data that looks like this: 

EventTable:

EventName	SystemId	EventQuoteNumber	eventDateTime
QuestionRequest	QWeb	24013730	9/10/2020
QuestionRequest	QWeb	24013731	9/10/2020
QuestionRequest	QWeb	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

Note that one quote number can have multiple rating requests

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 and

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

[prerakdt]

Did you add master time dimension table?

I tried below script, that is giving me correct count. You might want to try it.

EventTable:
Load * Inline
[
EventName,SystemId1,QuoteNum,eventDateTime
QuestionRequest,QWeb,24013730,'9/10/2020'
QuestionRequest,QWeb,24013731,'9/10/2020'
QuestionRequest,QWeb,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:
Load * Inline
[QuoteNum,SystemId,Status,quote_date
2417,LifeWeb,Accepted,'8/13/2020'
2424,LifeWeb,Accepted,'8/20/2020'
2811,LifeWeb,Accepted,'8/9/2020'
];

tmpDateBridge:
Load
QuoteNum,
Date(quote_date) as CanonicalDate,
'Quote' as DateType
Resident QuoteTable;

Load
QuoteNum,
Date(eventDateTime) as CanonicalDate,
'Event' as DateType
Resident EventTable;

NoConcatenate
DateBridge:
Load * Resident tmpDateBridge where IsNull(CanonicalDate) = 0;

Drop Table tmpDateBridge;

tmp:
Load Min(CanonicalDate) as minCanonicalDate, Max(CanonicalDate) as maxCanonicalDate Resident DateBridge;

Let vMinDate = Num(Peek('minCanonicalDate',0,'tmpDateBridge'));
Let vMaxDate = Num(Peek('maxCanonicalDate',0,'tmpDateBridge'));
Drop table tmp;

TempCalendar:
LOAD
$(vMinDate) + Iterno()-1 As Num,
Date($(vMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

MasterCalendar:
Load
TempDate AS [Canonical Date],
TempDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Date(Date#(Year(TempDate)&'-'&Month(TempDate),'YYYY-MMM'),'YYYY-MM') as YearMnth,
Day(TempDate) As Day,
// ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;

Drop Table TempCalendar;