topic Re: Dynamic bucket in QlikView

Dynamic bucket

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

Hi,

I need to create a bucket for my invoice spend ranging from £0.25m - £5m, £0.5m-£1m and so on. But this bucket should be dynamic on my selection of the currency which are USD($) and GBP(£). Is it feasible to create this on UI level opr should i create it on Data Model level?

Please suggest which one and how?

Thanks.

Re: Dynamic bucket

karthikoffi27se — Tue, 05 Sep 2017 07:09:46 GMT

Hi Abhishek,

Please use the class function for your ranging value.

You can use the class either in script or in UI.

Many Thanks

Karthik

Re: Dynamic bucket

suryaa30 — Tue, 05 Sep 2017 07:09:56 GMT

Do you have separate fields for different currencies values?? like InvoiceUSD ,InvoiceGBP

also should the bucket size be dynamic??

Refer my previous post for sample dynamic bucket using class function

Bucketing in bar Chart-Class function

Re: Dynamic bucket

Anonymous — Tue, 05 Sep 2017 08:11:53 GMT

HI Surya,

I don't have different fields. I just have InvoiceGBP which gets converted in USD according to a currency conversion rate coming from a different source.

The bucket size is not dynamic.

Re: Dynamic bucket

effinty2112 — Tue, 05 Sep 2017 08:46:20 GMT

Hi Abhishek,

You could use the class function as suggested by Karthik but this would restrict your buckets to a fixed size. You could try something like:

Pick(Match(Currency,'GBP','USD',

Pick(Match(-1,

Spend < =250000,

Spend < =500000,

Spend < =1000000,

-1),

'£0-£0.25m',

'£0.25-£0.5m',

'£0.5-£1m',

'>£1m'),

Pick(Match(-1,

Spend *ConversionRate< =250000,

Spend*ConversionRate < =500000,

Spend*ConversionRate < =1000000,

-1),

'$0-$0.25m',

'$0.25-$0.5m',

'$0.5-$1m',

'>$1m'))

Cheers

Andrew