topic Re: Bucket for month in QlikView

Bucket for month

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Bucket for month

lironbaram — Tue, 20 Dec 2016 07:59:49 GMT

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')))))

Re: Bucket for month

Gysbert_Wassenaar — Tue, 20 Dec 2016 08:12:26 GMT

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') ))))

Re: Bucket for month

Anonymous — Tue, 20 Dec 2016 10:05:42 GMT

Hi Gysbert,

I am getting wrong values against bucket

Please find the expected out put with exact OS amount.

Re: Bucket for month

Gysbert_Wassenaar — Tue, 20 Dec 2016 11:16:10 GMT

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'))
        )
      )
    )
  )

Re: Bucket for month

Anonymous — Wed, 21 Dec 2016 03:16:27 GMT

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

Re: Bucket for month

Gysbert_Wassenaar — Wed, 21 Dec 2016 07:58:16 GMT

See attached example.