Creating Dynamic Buckets in Qlik Sense Visual

[spjuza]

I've been struggling to implement buckets in a Pivot Table visual, and I'm not sure the best way to do this. 

Data:

Measure: Price Change from Last Year

Dimension 1: Part Number

Dimension 2: Type of Part

What I am trying to do is to create a way to group the Part Numbers in each Type of Part into four groups, based on the Price Change from Last Year.

I have tried to implement this several different ways, using the Class function (didn't work, because I need different group cutoff points for different part numbers), using a series of If statements + Fractile command (e.g. if Price Change <= Fractile (Price Change, .25), 'Bottom 25%....)

I found another Qlik Sense thread that suggested something like this:

=Aggr(
Pick(
Ceil(
(4*Rank([Price Change],[Part Number])/Count([Part Number]),4)
/
Count(distinct total [Part Type])
),
'1st quartile','2nd quartile','3rd quartile','Bottom quartile'
),
[Part Number]
)

But that also doesn't seem to work.  This would be my preferred solution if I can get it to work.  Any thoughts on how best to implement these buckets?

[hic · Accepted solution]

I think you are on the right track. But first some questions:
* I assume that each [Part Number] has one and only one [Price Change]
* I assume that there are several [Part Number]s in each [Part Type]
* I assume that you want to make the grouping by looking at how each [Part Number] compares to other ones within the [Part Type]

The expression has some problems:
* The second parameter of rank should be an integer 0..4 and cannot be a field reference.
* The Aggr() should probably have two dimensions: [Part Number] and [Part Type]

So my guess is that you should use

=Aggr(
Pick(
Ceil(
4*Rank(Avg([Price Change]),4)
/
Count(distinct total <[Part Type]> [Part Number])
),
'1st quartile','2nd quartile','3rd quartile','Bottom quartile'
),
[Part Type],[Part Number]
)

I might be wrong about the order of the dimensions, so if it doesn't work, try changing [Part Type],[Part Number] to [Part Number],[Part Type].

[spjuza]

Your assumptions are correct.  Each Part Number has one price change, multiple part numbers exist in each Part Type, and I'm wanting the groups based on how each Part Number compare to others in Part Type. 

That's closer, but unfortunately not all groups are created.  With this particular part type, it only created three quartiles. 

When there are no filters applied, it only creates the 1st and 2nd quartile, which is odd since when you filer for a certain part type, it does have part numbers in the 3rd and Bottom quartile. 

Any thoughts on what is causing this?

[spjuza]

Ah, actually, I think it was because there are some parts that were not purchased in the most recent year.  When I filter for parts that have been purchased in both years, I get four buckets for each part type. 

Thanks, I think this solved it!