Select top 3 cases

Hi Mukesh,

correct - the INDEX_ORDER is applied before the filter, just like with PU functions.

But you can put a FILTER_TO_NULL around the INDEX_ORDER argument to ensure the filter is taken into account by INDEX_ORDER.

Best

David

Hi David,

Below formula I used to achieve this.

Single Number KPI - SUM(Complete_DataFrame.revenue)

Component Filter -

FILTER INDEX_ORDER(PU_COUNT(DOMAIN_TABLE(HOURS(Complete_DataFrame.Created)),HOURS(Complete_DataFrame.Created)),

ORDER BY (PU_COUNT(DOMAIN_TABLE(HOURS(Complete_DataFrame.Created)),HOURS(Complete_DataFrame.Created)) DESC)) <= 3;

it giving me same value for different date filters also.

Regards

Mukesh

Hi Mukesh,

ok, so when using it inside a FILTER, you cannot change it such that it respects other FILTERs. This is because filters in Celonis are stable, which means that they cannot influence each other. If they would, the order of the filters would matter, which would again be highly confusing for users, because the order of filters (and selections!) would matter. This is also the reason why PU functions dont take filters into account.

However, if you have other Analysis/Sheet/Component filters which you want to apply, you can write a CASE WHEN inside the INDEX_ORDER with those filter conditions, and set all rows which dont fulfill the condition to NULL. NULL values will not be taken into account by the INDEX_ORDER function, and that filter would be independent.

The other possibility would be to not use the INDEX_ORDER inside the FILTER, but inside the KPI itself. By doing this, you can use FILTER_TO_NULL. So use a Single KPI without a component Filter, and write this as a KPI:

SUM( CASE WHEN

INDEX_ORDER(PU_COUNT(DOMAIN_TABLE(HOURS(Complete_DataFrame.Created)), FILTER_TO_NULL (HOURS(Complete_DataFrame.Created))),

ORDER BY (PU_COUNT(DOMAIN_TABLE(HOURS(Complete_DataFrame.Created)),HOURS(Complete_DataFrame.Created)) DESC)) <= 3

THEN

Complete_DataFrame.revenue

ELSE NULL END

)

This should calculate your KPI while taking filters and selections into account.

Best

David