I have created a KPI to rank all plants based on their Invoice value. I also added two filters to this KPI - one for the top 20% of plants and the other for the remaining plants. Although these filters work as intended at the global level, they do not generate accurate numbers when applied to a specific country or region. How can I resolve this issue
Solved
Hello Everyone,I am trying to create a filter for a site which can be filtered at global, country, region... level as well.
Best answer by Sverre Klein
Hi @deeksha.r12,
I think you can use the DOMAIN_TABLE function, since you are retrieving both plants, country, and region from the case table if I understand correctly.
Here is the documentation. https://docs.celonis.com/en/domain_table.html
It would then be like this I'd assume.
(0.2*PU_COUNT(DOMAIN_TABLE("caseTable"."plant_column" ) , "caseTable"."Countrycolumn" ))
So in this example, IT calculates the threshold for the top 20% of plants within each country.
Hopefully this helps!
Kind regards,
Sverre Klein
+14Hi @deeksha.r12,
Could you provide the filter you have currently configured for Country or Region?
Kind regards,
Sverre Klein
Hi Klein,
The filter that I have used to display top 20% of plants is the plant Rank KPI <= (0.2*PU_COUNT (Plant from case table))
Country and Region are straight away from the case table
Hi @sverre.klein11 ,
Sorry, I didn't quite understand what you meant by 'filters for us.' Could you please clarify?
+14Hi @deeksha.r12,
I think you can use the DOMAIN_TABLE function, since you are retrieving both plants, country, and region from the case table if I understand correctly.
Here is the documentation. https://docs.celonis.com/en/domain_table.html
It would then be like this I'd assume.
(0.2*PU_COUNT(DOMAIN_TABLE("caseTable"."plant_column" ) , "caseTable"."Countrycolumn" ))
So in this example, IT calculates the threshold for the top 20% of plants within each country.
Hopefully this helps!
Kind regards,
Sverre Klein
Hi @sverre.klein11 ,
Thank you very much for the proposed solution, it works when I only want to filter the plants in a specific region/Country/Business unit. What I am looking to implement is to have filter that would give me top 20% of plants at a global level, region level, country level and Business level. I used the DOMAIN_TABLE function at the highest layer of the organizational hierarchy but still the numbers are not right.
+14Hi @sverre.klein11 ,
Thank you very much for the proposed solution, it works when I only want to filter the plants in a specific region/Country/Business unit. What I am looking to implement is to have filter that would give me top 20% of plants at a global level, region level, country level and Business level. I used the DOMAIN_TABLE function at the highest layer of the organizational hierarchy but still the numbers are not right.
Hi @deeksha.r12,
In that case, maybe play around with the CONSTANT function together with the DOMAIN_TABLE. CONSTANT() aggregates all values from a source column to a single value. Here is the documentation. https://docs.celonis.com/en/constant.html
Let me know if you need more assistance, happy to help!
Kind regards,
Sverre Klein
Hi @sverre.klein11 ,
Thank you for you response.
The present filter that I have is: FILTER PLANT RANK KPI <= PU_COUNNT(CONSTANT(), Casetable.plant) which works fine in overall case but there is slight deviation when selected for other dimensions such as business line, region etc. Also CONSTANT() with domain table cannot be used.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.