Bucket for month

[Anonymous]

Hi All,

I have written this script

if( today()-[Due Date]<=0, 'Not Due',  If(today()-[Due Date]<=180, '1-180', 'Above 180')) as Bucket,

for bucket but I want bucketing like below

Not Due

01-10-16\..30-11-16

01-08-16\..30-09-16

01-06-16\..31-07-16

Before 01-06-16

and every month it will get change  like this report generate till 30.11.2016 if user select 31.12.2016 then 01-06-16\..31-07-16 values update in Before 01-07-16

Thanks

[lironbaram]

you can do something like that

if (today-[Due Date] <=0 ,'Not Due' ,

  if([Due Date] <= monthend(addmonths(today(),-1)) and [Due Date] >= monthstart(addmonths(today(),-2))                           ,date(monthstart(addmonths(today(),-2)),'DD-MM-YY') & \  date(monthend(addmonths(today(),-1)),'DD-MM-YY'),.

       if ([Due Date] <= monthend(addmonths(today(),-3)) and [Due Date] >= monthstart(addmonths(today(),-4))                           ,date(monthstart(addmonths(today(),-4)),'DD-MM-YY') & \  date(monthend(addmonths(today(),-3)),'DD-MM-YY'),

     if ([Due Date] <= monthend(addmonths(today(),-5)) and [Due Date] >= monthstart(addmonths(today(),-6))                           ,date(monthstart(addmonths(today(),-6)),'DD-MM-YY') & \  date(monthend(addmonths(today(),-6)),'DD-MM-YY'),

       'Before ' & date(monthstart(addmonths(today(),-6)),'DD-MM-YY')))))

[Gysbert_Wassenaar]

Something like this:

( today()-[Due Date]<=0, 'Not Due',
  if(InMonth([Due Date], today(),-1) or InMonth([Due Date], today(),-2), Date(MonthStart(Today(),-2),'DD-MM-YYYY')  & '\..' & Date(MonthEnd(Today(),-1),'DD-MM-YYYY'),
    if(InMonth([Due Date], today(),-3) or InMonth([Due Date], today(),-4), Date(MonthStart(Today(),-4),'DD-MM-YYYY')  & '\..' & Date(MonthEnd(Today(),-3),'DD-MM-YYYY'),
      if(InMonth([Due Date], today(),-5) or InMonth([Due Date], today(),-6), Date(MonthStart(Today(),-6),'DD-MM-YYYY')  & '\..' & Date(MonthEnd(Today(),-5),'DD-MM-YYYY'),
        'Before ' & Date(MonthStart(Today(),-6),'DD-MM-YYYY') ))))

[Anonymous]

Hi Gysbert,

I am getting wrong values against bucket

Please find the expected out put with exact OS amount.

[Gysbert_Wassenaar]

Your data does not contain records to create buckets for 1-6-2016/..31-7-2016 and 1-10-2016/..31-11-2016. So you won't see those buckets in the chart because they can't be created from your data. The hard limit of 1-6-2016 should be moved up in the nested-if tree:

if(today()-[Due Date]<=0, 'Not Due', If([Due Date] < MakeDate(2016,6), 'Before ' & Date(MonthStart(Today(),-6),'DD-MM-YYYY'),    
  if(InMonth([Due Date], today(),-1) or InMonth([Due Date], today(),-2), Date(MonthStart(Today(),-2),'DD-MM-YYYY')  & '\..' & Date(MonthEnd(Today(),-1),'DD-MM-YYYY'), 
    if(InMonth([Due Date], today(),-3) or InMonth([Due Date], today(),-4), Date(MonthStart(Today(),-4),'DD-MM-YYYY')  & '\..' & Date(MonthEnd(Today(),-3),'DD-MM-YYYY'),
      if(InMonth([Due Date], today(),-5) or InMonth([Due Date], today(),-6), Date(MonthStart(Today(),-6),'DD-MM-YYYY')  & '\..' & Date(MonthEnd(Today(),-5),'DD-MM-YYYY'))
        )
      )
    )
  ) 

[Anonymous]

Can you please explain that How can I used this bucket an as expression.

[Gysbert_Wassenaar · Accepted solution]

See attached example.