Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')))))
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') ))))
Hi Gysbert,
I am getting wrong values against bucket
Please find the expected out put with exact OS amount.
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'))
)
)
)
)
Can you please explain that How can I used this bucket an as expression.
See attached example.