Hi @Abhitndn
you can use INDEX_ORDER to index the result based on the value, and then filter on those rows where INDEX_ORDER returns a 1. You can also use PU_MAX to get the maximum value for each group, and then compare it with the value of every row.
Hard to give more detailed information without knowing the PQL query of the original table. I assume that the %Cases value contains an aggregation. As you cannot use regular aggregations in a FILTER statement, you first need to replace this with a PU aggregation. Also it is not clear to me if you want to have the maximum value for each service affected or symptom_category or both.
Best
David
Thanks, David that really helps. So, % cases are calculated based on (count of cases resolved by chr_teamresolve/total number of cases).
Regarding your second query, I want to calculate the maximum value based on chr_teamresolve column. For eg. team xyz resolved maximum ( 70% )of cases where service affected is digital and symptom category is hard down. Another example would be team def because it resolved maximum cases for equpment and all out. So aggregation should be based on service_affected and symptom_category both
so first you need to re-create the %Cases KPI with PU functions instead of regular aggregations. As I dont know how your DM looks like (which tables are involved here), its hard to provide an exact query, but you probably need to do something like
PU_COUNT(DOMAIN_TABLE(chr_teamresolve), case_id) / PU_COUNT(CONSTANT(), "CaseTable"."CaseId")Check out the documentation for PU functions to get more information about how to use them.
As soon as you have done this, you can put it inside a INDEX_ORDER and put it inside the FILTER:
FILTER INDEX_ORDER(
PU_COUNT(...)/PU_COUNT(...),
ORDER BY ( PU_COUNT(...)/PU_COUNT(...) DESC ),
PARTITION BY ( service_affected, symptom_category )
) = 1;
Best
David
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.