Hi!

I’Ve been struggling with setting up the correct PQL for visualising a KPI on a line chart showing a timeline as the formula became quite complicated. Would appreciate any help on understanding what query I need or why it is not working. 

The KPI I want to display on a timeline chart is a cancellation rate. The view contains an input dropdown as a variable  ${outlier_analysis_selection_main.pql} which the dashboard should adjust to. The dimensions of the line chart are ROUND_MONTH("CASE_TABLE"."DSL_ORDER_DT"). 

In the line chart, I already have the overall cancellation rate.This does not adjust to any user filtering done through the dashboard, nor the selection through ${outlier_analysis_selection_main.pql}. I also have another cancellation rate line which does adjust to any user filtering done. I can therefore compare the development of the cancellation rate for whatever is selected to the overall cancellation rate.

Now I want to add a line that always shows me the cancellation rate of the top 20% performers, based on the selected ${outlier_analysis_selection_main.pql} while making sure only regard cases with more than 100 orders. This line should also not adjust to any user filtering in the dashboard. Below is the PQL formula I have now but which is not working as expected becuase of the CONSTANT(). This obviously disregards the months and gives me the same value for each month. I cannot figure out however how to adjust it to make it work. I cannot pass ROUND_MONTH("CASE_TABLE"."DSL_ORDER_DT") instead of CONSTANT() as this does not work with PU_Quantile. I cannot pass DOMAIN_TABLE (${outlier_analysis_selection_main.pql}) as I then have duplicated domain tables which also gives me an error. Any help is appreciated!
 

PU_QUANTILE( CONSTANT(),

    PU_count_distinct(DOMAIN_TABLE (${outlier_analysis_selection_main.pql}), "_cel_O2A_activities"."_CASE_KEY"

      , "_cel_O2A_activities"."ACTIVITY" IN (

          'Auftrag storniert',

          'BP Update Failed (COM)',

          'Storniert-technischer Grund',

          'Bonitätsprüfung negativ (BDSG)',

          'Bonitätsprüfung negativ',

          'Auftrag fehlerhaft',

          'Closed-Invalidated',

          'Portierung abgelehnt',

          'Storniert-technischer Grund CC',

          'Bereitstellung abgelehnt'

          )

        )

    /

    PU_count_distinct(DOMAIN_TABLE (${outlier_analysis_selection_main.pql}),

          "_cel_O2A_activities"."_CASE_KEY"

    ), 0.2,

  PU_COUNT_DISTINCT(

    DOMAIN_TABLE(${outlier_analysis_selection_main.pql}),

    "_cel_O2A_activities"."_CASE_KEY"

  ) > 100

    )