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

    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

      error loading image

       

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

       

      Thank & Regards

        • How to do with Set Analysis
          Miguel Angel Baeyens de Arce

          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,

              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

                • How to do with Set Analysis
                  Miguel Angel Baeyens de Arce

                  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
                          Miguel Angel Baeyens de Arce

                          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.