Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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
Not applicable

Re: Distinct Customers last 3 months rolling

Try the attached qvf

9 Replies
sunny_talwar
Not applicable

Re: Distinct Customers last 3 months rolling

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

maxgro
Not applicable

Re: Distinct Customers last 3 months rolling

maybe you can find some help here?

Distinct count of customers in last 3 months

Not applicable

Re: Distinct Customers last 3 months rolling

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
Not applicable

Re: Distinct Customers last 3 months rolling

Something like this would work?

Capture.PNG

Not applicable

Re: Distinct Customers last 3 months rolling

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
Not applicable

Re: Distinct Customers last 3 months rolling

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

Re: Distinct Customers last 3 months rolling

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
Not applicable

Re: Distinct Customers last 3 months rolling

Try the attached qvf

Not applicable

Re: Distinct Customers last 3 months rolling

Worked! Thanks Sunny!