Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Creating buckets

Hello community,

How to make buckets as per the below image:

ibm1.PNG

ibm.PNG

Thankyou

9 Replies
Highlighted
Specialist II
Specialist II

Hi,

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

Highlighted
Partner
Partner

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

Highlighted
Creator II
Creator II

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.

Highlighted
Specialist III
Specialist III

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 .

Highlighted
Specialist
Specialist

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

Tomasz

Highlighted
Master
Master

Hi Kshitija,

look at this: Class Function

i hope that helps

Beck

Highlighted
Specialist III
Specialist III

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

Highlighted

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor
Contributor

=(If(Count(Beds) < 21, ('20 or Below'),
If(Count(Beds) < 31, ('21-30'),
If(Count(Beds) < 51, ('30-50'),
If(Count(Beds) < 101, ('50-100'),
If(Count(Beds) < 501, ('100-500'), ('500+')))))))

 

This is working fine if i am using it in table at front end. How to create it as a field at script level?

as i want to use bucket as a dimension in box plot.