Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kshitijas
New Contributor III

Creating buckets

Hello community,

How to make buckets as per the below image:

ibm1.PNG

ibm.PNG

Thankyou

8 Replies
anushree1
Contributor II

Re: Creating buckets

Hi,

Could you please more specific as the image does not seem to have any bucketing across any dimension

omarbensalem
Esteemed Contributor

Re: Creating buckets

Your bucketing is not clear? is it a days bucketing or an Amout bucketing?

Well I'll answer both cases:

AMOUNT BUCKETING:

I'd suggest sthing like this:

as dimension : class(InvoiceAmount, 30 ,'Amount') as a dimension :

Per analogy:

Capture.PNG

Or you can have more control :

if(((InvoiceAmount)>0 and (InvoiceAmount)<=30),('0-30'),

if(((InvoiceAmount)>30 and (InvoiceAmount)<=60),('30-60'),

if(((InvoiceAmount)>60 and (InvoiceAmount)<=90),('60-90'),

'>90'

)))

)


DAYS BUCKETING:



Now, if you want to calculate the number of days (today-date) , I'd suggest you to do sthing like this in the script:

load *, if(Days<=30,'0-30' ,

if(Days<=60,'30-60',

if(Days<=90,'60-90',

if(Days<=120,'90-120','Over 120')))

) as intervalles;

Load *,

        Today()-DueDate as Days;

yourtable:

load * from source;

Then in the presentation, choose intervalles as dimension and you'll have sthing like this:

Capture.PNG

ishanbhatt
Contributor II

Re: Creating buckets

Hi Kshitija,

There are two ways to create a bucket.

1. Create a field for the bucket in the script.

Ex.

if(Today()-[Invoice Due by]>90,'>90',

     if(Today()-[Invoice Due by]>59,'60-90',

          if(Today()-[Invoice Due by]>29,'30-60',

               if(Today()-[Invoice Due by]>=0,'0-30','Open')))) As DueDaysBucket.


Make sure your default Date format and {invoice Due by] field format is same.

You can change the value of above expression as per your requirements.


2. Using Class function.

Ex.


if ( Value > 0 , class(Value,10))


Hope this is helpful.

agni_gold
Valued Contributor

Re: Creating buckets

Please go through CLASS function in qlik for bucketing  ,

Creating Aging Buckets within QlikView

Above link might help you , else can you please provide some sample data in excel. then we can help very easily .

tomasz_tru
Valued Contributor

Re: Creating buckets

When use Class, it's good to wrap it by Replace function to get rid ot this  '<= sales < '.

Tomasz

beck110979
Valued Contributor III

Re: Creating buckets

Hi Kshitija,

look at this: Class Function

i hope that helps

Beck

sinanozdemir
Valued Contributor III

Re: Creating buckets

Hi,

As others suggested that use of Class() function would be a good fit. I tend to use it in the load script:

Capture.PNG

And in your pivot table, you can use the buckets field in the set analysis:

Capture.PNG

Capture.PNG

I am also attaching the qvf file for your reference.

Thanks

Re: Creating buckets

Yes, That's the easy way as i feel. May be this?

Replace(Class(sales, 5, 'Sales'),'<= sales', '-')