topic Re: Not getting proper bucket in QlikView

Not getting proper bucket

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

Dear All,

I am not getting proper bucketing that is 'Above 181 ' is missing.

Thanks

Re: Not getting proper bucket

tresB — Wed, 30 Nov 2016 05:23:36 GMT

Try a simpler expression like:

LOAD

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

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

Re: Not getting proper bucket

Anonymous — Wed, 30 Nov 2016 05:25:55 GMT

Hi Vijay,

Apply following in script

if( interval(date(Today(),'DD-MM-YYYY')- date#(date([Due Date],'DD-MM-YYYY'),'DD-MM-YYYY'))<=0 ,'Not Due',

if( interval(date(Today(),'DD-MM-YYYY')- date#(date([Due Date],'DD-MM-YYYY'),'DD-MM-YYYY'))<=180,'91-180',

if( interval(date(Today(),'DD-MM-YYYY')- date#(date([Due Date],'DD-MM-YYYY'),'DD-MM-YYYY'))>=181,'Above 181'))) AS Bucket

Please find attached QVW for detailed information.

Thanks,

Re: Not getting proper bucket

Anonymous — Wed, 30 Nov 2016 05:55:57 GMT

Hi Tresesco,

Very helpful.

Now I want like this..

Balance

Not Due

1 - 30 days

31 - 60 days

61 - 90 days

More than \90 days

Note: Balance is sum([Amount (LCY)])

Re: Not getting proper bucket

Anonymous — Wed, 30 Nov 2016 05:56:59 GMT

Hi,

it's better to calculate difference between dates separately in other filed and then use that date filed in if..condition to create buckets.

if you provide your..date fields...we will give you better solutions.

Re: Not getting proper bucket

jonathandienst — Wed, 30 Nov 2016 05:57:18 GMT

Use this expression in the load - I have removed all the unnecessary formatting and flooring:

If(Today() - Floor([Due Date]) <= 0 , 'Not Due',

If(Today() - Floor([Due Date]) <= 90 , '0-90',

If(Today() - Floor([Due Date]) <= 180, '91-180', 'Above 181'))) as Bucket,

Re: Not getting proper bucket

Anonymous — Wed, 30 Nov 2016 06:07:56 GMT

Hi Jonathan,

I am looking for this..

Balance

Not Due

1 - 30 days

31 - 60 days

61 - 90 days

More than \90 days

Re: Not getting proper bucket

Anonymous — Wed, 30 Nov 2016 06:09:47 GMT

hi vijay.

your requirement is to create a..buckets with 1-30 and 31-60 and 61-90....

these all equal size of buckets..

you can also calculate..buckets with class function to get same size of buckets instead of using if condition.

through if condition we can calculate different sizes of buckets and as well as equal sizes of buckets

where is class function with simple code we can calculate equal size of buckets..