9 Replies Latest reply: Mar 11, 2011 9:05 AM by Miguel Angel Baeyens de Arce

# How to do with Set Analysis

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

Thank & Regards

• ###### How to do with Set Analysis

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

• ###### How to do with Set Analysis

Hi Miguel,

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

• ###### How to do with Set Analysis

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.

• ###### How to do with Set Analysis

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?

• ###### How to do with Set Analysis

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.

• ###### How to do with Set Analysis

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

• ###### How to do with Set Analysis

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.

• ###### How to do with Set Analysis

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.

• ###### How to do with Set Analysis

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.