topic Re: Count from Different dates in QlikView

Count from Different dates

sumeet-vaidya — Wed, 02 May 2018 11:34:54 GMT

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

Re: Count from Different dates

sunny_talwar — Wed, 02 May 2018 11:38:26 GMT

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

Re: Count from Different dates

sumeet-vaidya — Wed, 02 May 2018 11:40:48 GMT

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

Re: Count from Different dates

sunny_talwar — Wed, 02 May 2018 11:45:45 GMT

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

Re: Count from Different dates

sumeet-vaidya — Fri, 04 May 2018 09:47:04 GMT

Hi Sunny,

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

Regards

Sumeet

Re: Count from Different dates

sunny_talwar — Fri, 04 May 2018 11:44:11 GMT

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.

Re: Count from Different dates

sunny_talwar — Fri, 04 May 2018 12:26:45 GMT

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;