topic Re: % Buckets in QlikView

% Buckets

Fri, 17 Jan 2014 11:07:40 GMT

Hi All,

I have a column named Percentage and the respective values under it. I am trying to create multiple buckets from the same like 0-10, 11-25, 26-50 and so on.

I tried to create the bucket using the if condition under the load statement but I could not succeed. Need some guidance.

Eg.

Percentage
0.20
0.10
0.02
0.53
0.25
0.34

Thanks in Advance

Re: % Buckets

Fri, 17 Jan 2014 11:14:36 GMT

you can use Class function like below:

Class(Percentage*100,10,'bucket',10)

Thanks,

Angad

Re: % Buckets

whiteline — Fri, 17 Jan 2014 11:28:31 GMT

Hi.

Create table with buckets: LeftBound, RightBound. BucketName

Then use:

IntervalMatch(Percentage)

LOAD

LeftBound,

RightBound

Resident Buckets;

to create link-table between buckets and facts.

Re: Re: % Buckets

Fri, 17 Jan 2014 15:09:59 GMT

Hi WhiteLine,

Can you please elaborate using an example.

I did try using if statement as follows:

Percentage as SKU_Percentage,

if( (Percentage*100) = 0, '0',

if( (Percentage*100) >=1 and (Percentage*100) <=10 , '1-10',

if( (Percentage*100) >=11 and (Percentage*100) <=25, '11-25',

if( (Percentage*100) >=25 and (Percentage*100) <=50, '26-50',

if( (Percentage*100) >=51 and (Percentage*100) <=75, '51-75',

if( (Percentage*100) >=76 and (Percentage*100) <=100, '76-100',

Percentage ))))))) as Bucket,

However I am getting wrong answer in the list box named Bucket. Attached is the result in Bucket dimension.

Re: % Buckets

jonathandienst — Fri, 17 Jan 2014 15:21:37 GMT

The result is due to the gaps between your bands (ie if Percentage*100 is between 0 and 1 (eg 0.1) or betwee, 10 and 11 (eg 10.5). Rather do it like this:

if((Percentage*100) = 0, '0',

if((Percentage*100) <=10 , '1-10',

if((Percentage*100) <=25, '11-25',

if((Percentage*100) <=50, '26-50',

if((Percentage*100) <=75, '51-75',

if((Percentage*100) <=100, '76-100',

Percentage ))))))) as Bucket,

HTH

Jonathan

Re: Re: % Buckets

Fri, 17 Jan 2014 15:26:15 GMT

Thanks Jonathan