Count from Different dates

[sumeet-vaidya]

Dear All,

Please find below scenario where need to calculate Mobile count

Date 1	Date 2	Mobile
01-04-2018	01-04-2018	1001
02-04-2018	01-04-2018	1002
02-04-2018	01-04-2018	1003
02-04-2018	02-04-2018	1003
03-04-2018	01-04-2018	1005
03-04-2018	02-04-2018	1006

Output Format

Date 1	Distinct Mobile Count Based on Date2
01-04-2018	4
02-04-2018	2
03-04-2018	0

stalwar1‌ plz help

Regards

Sumeet

[sunny_talwar]

Why not use Date2 as dimension here? I think the best way to handle this would be to introduce Canonical Date in your data model

[sumeet-vaidya]

Hi Sunny,

As per the requirement, Date 1 has to be the dimension because there are several other expressions as well associated with that.

Regards

Sumeet

[sunny_talwar]

And this is what I thought would be the case... I think you def. need to implement Canonical Date in your date model to get this resolved

[sumeet-vaidya]

Hi Sunny,

Unable to find desired solution through Canonical Date. Could you please upload demo app for reference.

Regards

Sumeet

[sunny_talwar]

I don't have a demo app I can share, but the above thread clearly list out the steps you have to do to make this work. I will try to work with your data above to give you a sample.

[sunny_talwar]

Check the attached

Basically Date here is a canonical date which can be use to plot Date1 or Date using set analysis.

1st Expression

=Count(DISTINCT {<Flag = {'Date 2'}>} Mobile)

2nd Expression

=Count(DISTINCT {<Flag = {'Date 1'}>} Mobile)

Script

Table:

LOAD * INLINE [

    Date 1, Date 2, Mobile

    01-04-2018, 01-04-2018, 1001

    02-04-2018, 01-04-2018, 1002

    02-04-2018, 01-04-2018, 1003

    02-04-2018, 02-04-2018, 1003

    03-04-2018, 01-04-2018, 1005

    03-04-2018, 02-04-2018, 1006

];

LinkTable:

LOAD Mobile,

[Date 1] as Date,

'Date 1' as Flag

Resident Table;

Concatenate (LinkTable)

LOAD Mobile,

[Date 2] as Date,

'Date 2' as Flag

Resident Table;

Temp:

LOAD Min(Date) as minDate,

Max(Date) as maxDate 

Resident LinkTable; 

LET varMinDate = Peek('minDate', 0, 'Temp'); 

LET varMaxDate = Peek('maxDate', 0, 'Temp'); 

DROP Table Temp; 

TempCalendar: 

LOAD $(varMinDate) + Iterno()-1 as Num, 

Date($(varMinDate) + IterNo() - 1) as TempDate 

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar:

LOAD TempDate as Date,

Week(TempDate) as Week,

Year(TempDate) as Year, 

Month(TempDate) as Month, 

Day(TempDate) as Day, 

YearToDate(TempDate)*-1 as CurYTDFlag, 

YearToDate(TempDate,-1)*-1 as LastYTDFlag, 

InYear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

DROP Table TempCalendar;