Static Variables

Hi All,

I would like to use a static variable for a % calculation, so that when filters are applied, the denominator will stay static. The issue that i am facing is that i would like the static variable to be static based on the local market and not the sum for all local markets.

Such as below:
image

Please let me know if this is possible using the static variable, and if not, please advise if there is another way i can achieve this.

All the best,
Anna

Hi Anna,

you can use PU functions for this. They allow to calculate an aggregation based on a grouper while not respecting FILTERs.
The formula depends on how your tables in the data model look like, but it should be something like this:
PU_COUNT( DOMAIN_TABLE("Table"."LocalMarket"), "Table"."RejectionVolume" )

Best
David

Hi David,

thanks for your quick response. I have amended the suggestion a bit, as i would like to pull the distinct number of cases into my calculation. However, with the below formula, i do not think it is taking into account to only include the case count where Rejection Flag=1., as the results bring in the total number of distinct cases.

COUNT(DISTINCT CASE WHEN (PU_COUNT_DISTINCT(DOMAIN_TABLE(CASES.“LocalMarket”, “CASES”.“RejectionFlag”), “CASES”.“RejectionFlag”) =1.0)
THEN “CASES”."_CASE_KEY" ELSE NULL END)

Please advise. :slight_smile:

Thanks
Anna

Hi David,

And one more thing, when i use the above formula, the count is not static, but does update based on the filters selected :frowning:

All the best,
Anna

Hi Anna,

here you are checking if the distinct rejection flag count equals 1, instead of counting how often the rejection flag equals 1. Since you’ve included the RejectionFlag also in the DOMAIN_TABLE, you are now counting the number of distinct RejectionFlags per LocalMarket and RejectionFlag, which is always 1. This is why the CASE WHEN condition is always true.

Can you please specify in more detail what the exact problem is which you are trying to solve? So which tables and columns are relevant, and how should the KPI be calculated exactly?

Best
David

Hi David,

I have an OLAP table that as a dimension has “CASES”.“LocalMarket” And for KPI is: Count(DISTINCT CASE WHEN “CASES”.“RejectionFlag” = 1.0 THEN “CASES”."_CASE_KEY" ELSE NULL END)

There are also several filter options in this dashboard. for example. a drop down for rejections reasons. When someone selects an item from this drop, i do not want those numbers in my KPI to change. (hence why i tried using the static variable)

I hope this is now more clear.

Thanks,
Anna

Hi Anna,

can you try to use the following KPI instead of the current one:

PU_COUNT_DISTINCT(DOMAIN_TABLE("Cases"."LocalMarket"), "Cases"."_CASE_KEY", "Cases"."RejectionFlag" = 1.0 )

This should be the same query, but only as a PU function, which does not respect the filters. Note that if a LocalMarket value is filtered out completely, you also will not see that as a row in the OLAP table. For every LocalMarket value that “survives” your filter, however, you should see the total number of cases with a RejectionFlag=1 for that LocalMarket.

Hope this works.
Best
David