QlikView : Dynamic row-level Fractile inside a Sum() expression in a List Chart – How to adapt per row?

[fredericvillemi]

Hi everyone,

I'm working on a List Chart in QlikView and I'm trying to replace a pre-calculated percentile field with a dynamic Fractile at 0.98, computed based on the current selections.

Context

My chart calculates a weighted average duration, but only for rows where a computed ratio is below a given percentile threshold. The original expression uses a pre-calculated field (pre_calculated_percentile) as the threshold:

=sum({<Type={CLOSED}>} 
  if(
    (field_duration / field_tickets) <= pre_calculated_percentile,
    field_duration,
    Null()
  )
)
/
sum({<Type={CLOSED}>} 
  if(
    (field_duration / field_tickets) <= pre_calculated_percentile,
    field_tickets,
    Null()
  )
)

What I tried

I replaced the pre-calculated field with a dynamic Fractile() using the TOTAL qualifier:

=sum(
  {<Type={CLOSED}>}
  if(
    (field_duration / field_tickets)
    <=
    Fractile(
      {<Type={CLOSED}>}
      TOTAL
      field_duration / field_tickets,
      0.98
    ),
    field_duration,
    Null()
  )
)
/
sum(
  {<Type={CLOSED}>}
  if(
    (field_duration / field_tickets)
    <=
    Fractile(
      {<Type={CLOSED}>}
      TOTAL
      field_duration / field_tickets,
      0.98
    ),
    field_tickets,
    Null()
  )
)

Problem

This approach does work partially, but the TOTAL qualifier computes a single global Fractile across all rows, instead of adapting the threshold per row (i.e., per dimension value displayed in the list).

I realle need to have the Fractile computed for each row independently, based on the data associated with that specific dimension value.

Question

Is there a way to make the Fractile() calculation dynamic per row in a List Chart, without pre-calculating the percentile in the data load script?

I've thought about using Aggr() but haven't found a working combination yet. Any ideas or workarounds would be greatly appreciated!

Please note that the List chart can have dynamic dimensions added or removed and thus I cannot know exactly which dimensions the Fractil will be computed on.

Thanks in advance 

[Greg_Taffer]

You can achieve a dynamic, row-level Fractile() calculation in a Qlik Sense List Chart (or table) without pre-calculating it in the script, even with dynamic dimensions. 
 

The key is to use the Aggr() function to create a virtual table that calculates the metric per dimension and then applies the Fractile() on top of those results, using the total qualifier within the inner aggregation to force independence from other chart dimensions. 

Recommended Solution: Fractile(Aggr())

To make this work with dynamic dimensions (where you don't know the exact dimension names), you must use the Aggr() function to force the calculation to happen at the correct level of granularity for every row, regardless of what dimensions are added to the list box, by using a specific, consistent field for the inner grouping.
 

Formula Structure:

Fractile(   
 Aggr(        Sum({<[DimensionField]=, [OtherField]= >} [MeasureField]
),
                  [DimensionField]    ),    

0.5 // Or your desired percentile
)

Why this will work for you:

• Aggr(..., [DimensionField]): This creates a virtual table, calculating the Sum([MeasureField]) independently for every unique value of [DimensionField], regardless of what is displayed in the list chart.
• Dynamic Nature: Because Aggr() handles the calculation of the measure at the row level of the dimension, you can add or remove other dimensions to your table/chart, and the calculation of the fractile will remain consistent with the level specified in Aggr().
• TOTAL usage: If you need to ignore selections on a specific field to keep the threshold consistent while filtering, you can insert TOTAL inside the Aggr: Aggr(Sum(TOTAL <[DimensionField]> [MeasureField]), [DimensionField]). 

[fredericvillemi]

Hello,

thanks for your message

I understand i need to put all dynamic dimensions in the set analysis of the sum inside the Aggr ?

But how can i put a list of dynamic dimension there or in the parameter of the Aggr ?

Could you try to use my examples to show me what i could do ?

Thanks