Set Analisys: Customers who not bought in the last 07/14/21/28 days

[pedrolyra]

Hey all, I have been breaking my head to make this set analysis and hope you guys can help me. As it is already stretching more than I imagined I come here to get some help of the professionals.

Basically what I need is a set analysis that when selecting the date, shows me the customers who did not make purchases in the previous 7 days, in the previous 14 days, in the previous 21 days and 28 days. One detail is that the same person can not be in the list of 7 and in the 14 or 21, etc.

Example:

Selected date: 4/21/2018

Purchases after the 04/21 day can not be considered;

Rick made purchase on 04/13/2018, he has to appear in the range of 7 days; If he had made the purchase on 04/14/2018 he would not appear;

Mark made purchase on 04/04/2018, he can not appear in the range of 7 days and only appear in the 14 days;

Pedro made a purchase on 03/30/2018, he can not appear in the range of 7 days, nor in 14, only in 21;

So on and on.

Hope you guys understand.

Have any of you ever done anything similar?

Thanks in advance for any help! Big hug!

I generated a base with id sale, id customer and date of purchase in case you guys can help me.

[cbushey1]

The problem here is that when a user selects a date, they are choosing from the same list of sale dates, therefore you would only get the people that had sales on the given date.

Without seeing your data model, if the field the user is selecting is a different date field, you could use set analysis something like p({<saledate = "<[date selected] - 7">} idCustomer). This would yield you all the possible idcustomer's that had a saledate at least 7 days before.

Another option, depending on what you are trying to do with this information, is to come up with a max sale date for each customer and compare that to the date selected. Then you could build your lists around when the last sale to the customer is.

[robert99]

Hi

This might work (or might not)

Use DaysAgo

Relative Calendar Fields

and if statements

A bit like this.

if(

sum ({<DaysAgo = { ">=$(=DaysAgo) <=$(=DaysAgo+7)" }  >}

Qty* Price) = 0, 'Yes' , 0)

and then turn off include zero values

Start with the first 7 days ABOVE then start the next 7 days with 

if(

sum ({<DaysAgo = { ">=$(=DaysAgo)", "<=$(=DaysAgo+7)" }  >}

Qty* Price) = 0, 0 , then do next 7 days )

[lfimotoo]

Hi Pedro.

Please, check if the attached document answers your needs.

Basically the expression is as follows:

• Did not purchase in the last 7 days:
  • Only({< idcustomer = E({< saledate = {">=$(=Date(<<FILTER>> - 7))<=$(=Date(<<FILTER>>))","<=$(=Date(<<FILTER>> - 14))"} >} idcustomer), saledate = {"<$(=Date(<<FILTER>>))"} >} idcustomer)

Where <<FILTER>> is your "selected date". In my example I created an unlinked dimension for this purpose, but it could be a variable as well.

Explaining the Set Analysis:

• idcustomer = E({< saledate = {">=$(=Date(<<FILTER>> - 7))<=$(=Date(<<FILTER>>))","<=$(=Date(<<FILTER>> - 14))"} >} idcustomer)
  • Excludes all idcustomer that either bought in the last 7 days or after 14 days.
• saledate = {"<$(=Date(<<FILTER>>))"}
  • Only customers that bought at least once
    

The expressions to "14 days", "21 days" and "28 days" can all be found in the attached document.

If performance is an issue, it may be interesting to analyze the possibility of calculating in the load script.

EDIT: This solution also works for Qlik Sense. Check the attached QVF example.

Best regards.

[rangam_s]

Please try this.

Editor:

Sales:

LOAD

    idcustomer,

    idsale,

    saledate

FROM [lib://dataset/datacustomersale.xlsx]

(ooxml, embedded labels, table is Sheet1);

Grouped:

Load idcustomer,max(saledate) as Maxdate,Min(saledate) as MinDate Resident Sales Group By idcustomer;

MaxDate:

load Max(saledate) as MDate Resident Sales;

Chart Expression:

if((Maxdate<(MDate-7) and Maxdate>(MDate-14)),'Not Purchased in last 7 days')

Same way you can calculate for 14,21 and 28

Hope this works for you...