Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Distinct Customers last 3 months rolling

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.

1 Solution

Accepted Solutions
sunny_talwar

Try the attached qvf

View solution in original post

9 Replies
sunny_talwar

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

maxgro
MVP
MVP

maybe you can find some help here?

Distinct count of customers in last 3 months

Not applicable
Author

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!

sunny_talwar

Something like this would work?

Capture.PNG

Not applicable
Author

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!

sunny_talwar

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;

Not applicable
Author

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;

sunny_talwar

Try the attached qvf

Not applicable
Author

Worked! Thanks Sunny!