Dynamic Buckets - Class function

[Tee_dubs]

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))
))))

[marcus_sommer]

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

[Tee_dubs]

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. 

[marcus_sommer]

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

[Tee_dubs]

I was thinking more an option to define the bucket size in the properties
panel (with and option of using an expression). This exists for histograms
but not Combo or bar chats.

The class function itself is fine.

[Tee_dubs]

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.

[marcus_sommer]

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