topic Re: Distinct Customers last 3 months rolling in App Development

Distinct Customers last 3 months rolling

Mon, 22 Aug 2016 19:32:20 GMT

Table:

LOAD *,

MonthName(Date) as MonthYear

INLINE [

Date, CustomerID

11/5/2014,3

11/25/2014,2

11/7/2014,1

11/30/2014,4

12/15/2014, 1

12/13/2014, 2

12/1/2014, 2

12/12/2014, 4

1/11/2015, 1

1/15/2015, 3

1/1/2015, 4

2/14/2015, 5

2/17/2015, 3

2/16/2015, 1

2/1/2015, 2

3/17/2015, 5

3/21/2015, 1

3/14/2015, 3

3/11/2015, 4

4/5/2015,2

4/15/2015,5

4/1/2015,1

4/28/2015,3

];

WANT:

April 2015 - 2

March 2015- 1

Feb 2015- 2

Jan 2015 - 0

Dec 2015 - 0

Nov 2015 - 0

I am ok with either solution. Front end or back end, whichever gets me the correct solution.

Re: Distinct Customers last 3 months rolling

sunny_talwar — Mon, 22 Aug 2016 20:24:04 GMT

What are those ID which belong to April, just so we understand what got included and how

Re: Distinct Customers last 3 months rolling

maxgro — Mon, 22 Aug 2016 20:28:14 GMT

maybe you can find some help here?

Distinct count of customers in last 3 months

Re: Distinct Customers last 3 months rolling

Mon, 22 Aug 2016 20:30:11 GMT

April - 2 (ID's are 3 and 1)

March - 1 (ID - 1)

Feb - 2 ( ID's 4 and 1(

Hope this helps explains it better. THANKS!

Re: Distinct Customers last 3 months rolling

sunny_talwar — Mon, 22 Aug 2016 20:39:00 GMT

Something like this would work?

Re: Distinct Customers last 3 months rolling

Mon, 22 Aug 2016 20:41:56 GMT

Yes, exactly that. Would you be able to send me the script. I am a Qlik Sense user, not able to open up a qlikview file. Thanks!

Re: Distinct Customers last 3 months rolling

sunny_talwar — Mon, 22 Aug 2016 21:24:27 GMT

Sure thing:

Table:

LOAD *,

MonthName(Date) as MonthYear

INLINE [

Date, CustomerID

11/5/2014,3

11/25/2014,2

11/7/2014,1

11/30/2014,4

12/15/2014, 1

12/13/2014, 2

12/1/2014, 2

12/12/2014, 4

1/11/2015, 1

1/15/2015, 3

1/1/2015, 4

2/14/2015, 5

2/17/2015, 3

2/16/2015, 1

2/1/2015, 2

3/17/2015, 5

3/21/2015, 1

3/14/2015, 3

3/11/2015, 4

4/5/2015,2

4/15/2015,5

4/1/2015,1

4/28/2015,3

];

FinalTable:

LOAD *,

MonthName(AddMonths(MonthYear, 1)) as New_MonthYear;

LOAD *,

If(AddMonths(Previous(Previous(MonthYear)), 2) = MonthYear and AddMonths(Previous(MonthYear), 1) = MonthYear and

CustomerID = Previous(Previous(CustomerID)) and CustomerID = Previous(CustomerID), 1, 0) as Flag;

LOAD DISTINCT

MonthYear,

CustomerID

Resident Table

Order By CustomerID, MonthYear;

DROP Table Table;

Re: Distinct Customers last 3 months rolling

Mon, 22 Aug 2016 22:01:36 GMT

This works with the original data, but breaks at this point.

The results should still be the same

May - 3 (ID's 3, 5, 1)

April - 2 (ID's are 3 and 1)

March - 1 (ID - 1)

Feb - 2 ( ID's 4 and 1(

Table:

LOAD *,

MonthName(Date) as MonthYear

INLINE [

Date, CustomerID

11/5/2014,3

11/25/2014,2

11/7/2014,1

11/30/2014,4

12/15/2014, 1

12/13/2014, 2

12/1/2014, 2

12/12/2014, 4

1/11/2015, 1

1/15/2015, 3

1/1/2015, 4

2/14/2015, 5

2/17/2015, 3

2/16/2015, 1

2/1/2015, 2

3/17/2015, 5

3/21/2015, 1

3/14/2015, 3

3/4/2015, 5

3/7/2015,5

3/11/2015, 4

4/5/2015,2

4/15/2015,5

4/1/2015,1

4/28/2015,3

];

FinalTable:

LOAD *,

MonthName(AddMonths(MonthYear, 1)) as New_MonthYear;

LOAD *,

If(AddMonths(Previous(Previous(MonthYear)), 2) = MonthYear and AddMonths(Previous(MonthYear), 1) = MonthYear and

CustomerID = Previous(Previous(CustomerID)) and CustomerID = Previous(CustomerID), 1, 0) as Flag;

LOAD DISTINCT

MonthYear,

CustomerID

Resident Table

Order By CustomerID, MonthYear;

DROP Table Table;

EXIT SCRIPT;

Re: Distinct Customers last 3 months rolling

sunny_talwar — Tue, 23 Aug 2016 00:25:35 GMT

Try the attached qvf

Re: Distinct Customers last 3 months rolling

Tue, 23 Aug 2016 14:19:28 GMT

Worked! Thanks Sunny!