Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
johnnyjohn
Creator
Creator

Create given number of buckets from a measure

Hello, I have the measure below.

Sum(n0_is_at_mid_bps * n0_value_EUR) / Sum(n0_value_EUR)

What is the best way to create, say 20 buckets for the this measure, dynamically as an expression, not in the load script.

The parameter to the class function doesn't allow this currently. 

Thanks

Labels (2)
1 Solution

Accepted Solutions
vincent_ardiet_
Specialist
Specialist

Ok, I see, you are applying this on a field, not a measure and to be used as a dimension.
Aggregation functions are not allowed here, however, the result of your computation is a constant, so you can use the variable expansion syntax.
Like this : 
CLASS(
n0_is_at_mid_bps,
$(=
CEIL((FABS(Min(n0_is_at_mid_bps)) + FABS(Max(n0_is_at_mid_bps))) / 20))
)

View solution in original post

11 Replies
vincent_ardiet_
Specialist
Specialist

If the bucket sizes are identical across the range, you can use Mod() to compute a modulo of your measure and combined with a Match() this could maybe do the trick.

johnnyjohn
Creator
Creator
Author

Could you provide an example? I do need the bucket sizes to be equal and the numbers from my measure may be positive or negative. Thanks 

vincent_ardiet_
Specialist
Specialist

In fact, this is not with MOD but with DIV, something like this for example:
if(sum(Expression1)>=0
,pick(rangemin(1+fabs(div(sum(Expression1),500)),7),'0 < 500','500 < 1000','1000 < 1500','1500 < 2000','2000 < 3000','2000 < 3000','>=3000')
,pick(rangemin(1+fabs(div(sum(Expression1),500)),7),'-500 < 0','-1000 < -500','-1500 < -1000','-2000 < -1500','-3000 < -2000','-3000 < -2000','<=-3000'))

Ideally, you can create the parameters of the pick in your loading script in a variable, which could be more dynamic. Note that I have created a 2000-3000 zone putting it twice in the series, so you can have unequal intervals if only they are multiples of the smallest one (here 500).

 

johnnyjohn
Creator
Creator
Author

Ah but I think this is my issue here - I don't want to hard set buckets to specific sizes. I want to create buckets of equal width according the values existing in the dataset (which should respect the filters).

So for example if I have 40 values ranging from 1 to 40, 20 baskets of 1-2, 2-3, 3-4, 18-19,19-20, etc... would be created. If I filtered my dataset and restricted the data such that my data would only have 1 of 20 values, 20 buckets would still be created, with 1, 2, 3, 4, etc... buckets. 

So not quite quartile bucketing, but strict setting of buckets according to the values available and the number of buckets to be set. 

I recall doing this a few years ago, and setting a slider as a variable that let me decide how many buckets to  show, but I can't recall how now. 

vincent_ardiet_
Specialist
Specialist

The only way is to use the Class function in this case.
What is the issue when you use it?

johnnyjohn
Creator
Creator
Author

Class function create how ever many bucket are needed to split the data by groups of x, where x is the input parameter. What I am trying to do is create a GIVEN, FIXED amount of buckets, where the edges of the buckets are delineated by splitting the data equally into that fixed number of bucket. Hope that makes sense

 

vincent_ardiet_
Specialist
Specialist

Try something like this, this is creating about 20 buckets above 0 or below 0, so 40 buckets if data are equally spread around 0 rounding to a multiple of 50 for buckets' size:
class(sum(Expression1),ceil(Div(rangemax(max(TOTAL aggr(sum(Expression1),Dimension1)),-min(TOTAL aggr(sum(Expression1),Dimension1))),20),50))

johnnyjohn
Creator
Creator
Author

I tried your suggest solution, with no luck. I tried to do something similar, with the expression below, which just looks at the absolute difference between the min and the max, and divide that by 20. This should in theory give me the size needed for each bucket to create 20 buckets, but the expression fails when using it as a dimension on a histogram or barchart for instance. Any ideas?

=CLASS(
n0_is_at_mid_bps,
CEIL((FABS(Min(n0_is_at_mid_bps)) + FABS(Max(n0_is_at_mid_bps))) / 20)
)

johnnyjohn
Creator
Creator
Author

Bump - if anyone can help with the expression below. When I render the second parameter into a KPI it shows me the number correctly - but it doesn't work when I use the number in the CLASS to define the width of the buckets.  

=CLASS(
n0_is_at_mid_bps,
CEIL((FABS(Min(n0_is_at_mid_bps)) + FABS(Max(n0_is_at_mid_bps))) / 20)
)