
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to write set analysis using Addmonth() ?
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
- Tags:
- set analysis
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this
Count(DISTINCT Aggr(If(SetDateYear(BillDate, Today()) >= AddMonths(SetDateYear(DOB, Today()), -1) and SetDateYear(BillDate, Today()) <= AddMonths(SetDateYear(DOB, Today()), 1), Year(BillDate)), CustomerCode, BillDate, DOB))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be try this
Count({$<[Bill.DDMM] = {">=$(=Date(AddMonths(Max([DOB]), -1), 'DD-MM'))<=$(=Date(AddMonths(Max([DOB]), 1), 'DD-MM'))"}>} DISTINCT Year(BillDate))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this.
count({$<[Bill.DDMM] = {">=$(=Date(addmonths(max([DOB]), -1), 'DD-MM'))<=$(=Date(addmonths(max([DOB]), 1), 'DD-MM'))"}>} distinct Year(BillDate))
Regards,
Kaushik Solanki

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunny & Kaushik,
I even tried it before it doesn't work. I just added the sample file.
Thank,
Mohan

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What is the expected output and in what type of object?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi sunny,
I am doing it in the straight table. Expected is straight forward in my expression which I wrote in question. To be more clear, To calculate How many years he purchased with me near to his DOB date.
Thanks!!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I was hoping to see a numeric form of output and not expressional form of output

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunny,
Expected table as follows,
Also please take a look at the Excel file attached with this reply.
I hope you can understand what I am looking after. Need help !!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this
Count(DISTINCT Aggr(If(SetDateYear(BillDate, Today()) >= AddMonths(SetDateYear(DOB, Today()), -1) and SetDateYear(BillDate, Today()) <= AddMonths(SetDateYear(DOB, Today()), 1), Year(BillDate)), CustomerCode, BillDate, DOB))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Or this
Count(DISTINCT Aggr(If(SetDateYear(BillDate, Today()) >= AddMonths(SetDateYear(DOB, Today()), -1) and SetDateYear(BillDate, Today()) <= AddMonths(SetDateYear(DOB, Today()), 1), Year(BillDate)), CustomerCode, BillDate))

- « Previous Replies
-
- 1
- 2
- Next Replies »