Creating buckets

[Karahs]

Hello community,

How to make buckets as per the below image:

Thankyou

[anushree1]

Hi,

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

[OmarBenSalem]

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:

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:

[ishanbhatt]

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]

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]

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

Tomasz

[beck_bakytbek]

Hi Kshitija,

look at this: Class Function

i hope that helps

Beck

[sinanozdemir]

Hi,

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

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

I am also attaching the qvf file for your reference.

Thanks

[Anil_Babu_Samineni]

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

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

[PoojaYadav]

=(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.