12 Replies Latest reply: May 24, 2017 4:09 AM by Mohan Kumar RSS

    How to write set analysis using Addmonth() ?

    Mohan Kumar

      Hi,

       

      I wanted to calculate the # of transaction from one month before the particular date and one month after the particular date irrespective of year. To explain it well, Let the particular date be 14-May-1995. You have a transaction data, I need to find whether the customer had any transaction in date between 14-Apr to 14-Jun in all year.

       

      I followed this thread, Using date ranges in set analysis and taken some Inputs. In a straight table, I wrote the set analysis as follows,

      count({$<[Bill.DDMM] = {">=(Date(addmonths(max([DOB]), -1), 'DD-MM'))<=(Date(addmonths(max([DOB]), 1), 'DD-MM'))"}>} distinct Year(BillDate)) it doesn't worked.

       

      To check my Date field, I wrote the if condition as follows,

      if([Bill.DDMM] > Date(addmonths(max([DOB]), -1), 'DD-MM') and [Bill.DDMM] < Date(addmonths(max([DOB]), 1), 'DD-MM'),1,0) it works fine. I came to know that there is something wrong with my set analysis. It taking my time to rectify the problem. Please help !!


      Thanks in Advance!!


      Mohan