Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am having an issue creating Dynamic Bucket sizes. For some dimensions it seems to work OK but for others I am getting overlapping buckets and variable bucket sizes.
This is the code that I have.
=IF([100INTERVAL_MO] <= 1,
dual(
replace(
class([Actual Int (Months)],0.1)
,'<= x <','-')
, class([Actual Int (Months)], 0.1))
,
IF([100INTERVAL_MO] <= 4,
dual(
replace(
class([Actual Int (Months)],0.25)
,'<= x <','-')
, class([Actual Int (Months)], 0.25))
,
IF([100INTERVAL_MO] <= 12,
dual(
replace(
class([Actual Int (Months)],0.5)
,'<= x <','-')
, class([Actual Int (Months)], 0.5))
// the example above matches this condition - I want to see even buckets at 0.5 Month intervals.
,
IF([100INTERVAL_MO] > 24,
dual(
replace(
class([Actual Int (Months)],1)
,'<= x <','-')
, class([Actual Int (Months)], 1))
,
dual(
replace(
class([Actual Int (Months)],1.5)
,'<= x <','-')
, class([Actual Int (Months)], 1.5))
))))
I believe you need to put your conditions to the different bucket-sizes within the class() and not using multiple ones. This means something like:
replace(
class([Actual Int (Months)],if([100INTERVAL_MO] <= 1, 0.1, if([100INTERVAL_MO] <= 4, 0.25, ...)))
,'<= x <','-')
and I think you don't need the dual() because class() is already dual.
- Marcus
I'll give that a go on Monday.
DUAL and REPLACE go together.
REPLACE on its own will give you a string which can't be sorted. Using DUAL then allows the buckets to be sorted correctly (as a number rather than a string).
In my opinion this is a flaw in Qlik Sense. There should be an option to define a bucket size by expression and have it display in a user friendly way rather than using the class statement.
Yes, that's right. Applying a string-function will always return a string.
Personally I do like the class-function like they are whereby a bit more flexibility to define the output-string would be definitely useful. But I'm not sure that's really a flaw that there is no direct way to define multiple bucket-sizes because it would be an essential change of the function and not more a rather simple function else a quite complex construct like the aggr-function and I see there more disadavantages as benefits.
- Marcus
So I have changed my code...
class([Actual Int (Months)],
IF([100INTERVAL_MO] <= 1, 0.1,
IF([100INTERVAL_MO] <= 4, 0.25,
IF([100INTERVAL_MO] <= 12, 0.5,
IF([100INTERVAL_MO] > 24, 1, 1.5)))))
I have removed DUAL and REPLACE. I am still getting mixed bucket sizes.
I know that I can use the AGGR function to bucket the dimension based on the measure calculation. However the volume of data I am aggregating means excessively long load times.
It looked that [100INTERVAL_MO] isn't distinct in regard to [Actual Int (Months)]. If so you may need further conditions and/or an aggr to get an unique relationship between them. You mentioned that you have rather a large dataset and it might be slow with it. In this case take a look on your datamodel if it could be optimized for it respectively in general (means among other to keep all used fields within one table).
- Marcus