How to do with Set Analysis

[#1]

Hi All,

I have a requirement to count the New Customer and the logic for that is the customer donot have any transaction in last 365 days

I write the below expression for that

=count( distinct {<APP_DATE = {">=$(=FromDate) <=$(=ToDate)"},

ID_EMAIL = e({<APP_DATE = {">=$(=FromDate-365) <$(=FromDate)"}>} ID_EMAIL)>} ID_EMAIL )

The above expression gives me the correct answer when single date is selected or for the first date from the date range and the answer varies for the rest for the date range. Please check the below screenshot

Please help me with your suggestions & solution, where I am going wrong?

Thank & Regards

[Miguel_Angel_Baeyens]

Hello,

Will it work if you say "count all customers except for those that have transactions 365 days ago to today?

Count({1 - < APP_DATE = {'>=$(=AddYears(Date(FromDate), -1))<=$(=Date(FromDate))'} >} DISTINCT ID_EMAIL)

Note that the FromDate and ToDate variables are now formatted as dates.

Hope that helps

[#3]

Hi Miguel,

Thanks for your reply, but my problem is not yet resolved.

I want to count all customers who donot have any transaction in last 365 day i.e when I select date range 1Feb2011 to 5Feb2011 then for 1Feb2011 it should search in 1Feb2010 to 31Jan2011 and similarly for 2Feb2011 it should search in 2Feb2010 to 1Feb2011 and so on.So that for any date range my data will not vary.

If you see my expression I have used the Indirect Set Analysis. So can you please help me what changes to the expression should I do so that I will give me the desire output.

Thanks & Regards

[Miguel_Angel_Baeyens]

Hello,

I'm assuming that one variable (vToday) stores the date when those 365 days start to count.

Count({< ID_EMAIL = E({< APP_DATE = {'>=$(=Date(vToday))<=$(=AddYears(vToday, 1))'} >} ID_EMAIL) >} DISTINCT ID_EMAIL)

Say

1.- vToday is set to 01/01/2010 (DD/MM/YYYY)

2.- ID_EMAIL "AA", "BB", "CC" have sales in year 2010

3.- ID_EMAIL "DD" have sales in 2006.

According to the expression above, your table should only return "DD".

Hope that helps.

[#5]

Hi Miguel,

As per my requirement 365 days should start according the date range (APP_DATE) selection

Below is the screen shot for ref

So what should the expression should I write?

[Miguel_Angel_Baeyens]

I'm afraid missing something here. Is APP_DATE a field that the user can select so the range is automatically calculated in variables? If so, what's the name of the variables? Or has the date range to be calculated depending on the selection in APP_DATE?

According to my example expression, you can use it

Count({< ID_EMAIL = E({< APP_DATE = {'>=$(=AddYears(vToday, -1))<$(=Date(vToday))'} >} ID_EMAIL) >} DISTINCT ID_EMAIL)

where vToday is the variable (not field) I'm using for the end of the range (equivalent to your APP_DATE).

Regards.

[#7]

Hi Miguel,

=count( distinct {<APP_DATE = {">=$(=FromDate) <=$(=ToDate)"},

ID_EMAIL = e({<APP_DATE = {">=$(=FromDate-365) <$(=FromDate)"}>} ID_EMAIL)>} ID_EMAIL )

Where FromDate & ToDate are two variable

In my application user has to select the period for which he wants the count of New Customer

Thanks & Regards

[Miguel_Angel_Baeyens]

Hi there,

Try

Count({< APP_DATE = {'>=$(=Date(FromDate))<=$(=Date(ToDate))'}, ID_EMAIL = E({< APP_DATE = {'>=$(=AddYears(FromDate, -1))<$(=Date(FromDate))'} >} ID_EMAIL) >} DISTINCT ID_EMAIL)

Formatting variables with Date() functions and using AddYears just to get a date format as well.

Hope that helps.

[#9]

Hi Miguel,

The expression which you have provide gives the answer but the issue still not resolved.

The count varies when date range is change.

Please check the below screenshot

[Miguel_Angel_Baeyens]

Hi,

It must change, since the filters the set analysis is using are different. It might not be the same number when the year is May2010-Apr2011, than when it's Jan2010-Dec2010, might it?

Note that you have two filters, basically

1.- Count those who do have transactions between FromDate and ToDate

2.- But exclude those who don't have one year before FromDate (although they may have the year before)

Regards.