Skip to main content
Question

Static Variables

  • August 26, 2020
  • 6 replies
  • 8 views
.
Level 6

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:

https://aws1.discourse-cdn.com/business6/uploads/celonis4/original/2X/9/9801196c0b58e72d2fe33239032928e257f0677c.pngPlease 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

6 replies

D
Level 1
Forum|alt.badge.img+2

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

.
Level 6
  • .14600775
  • Author
  • Level 6
  • August 27, 2020

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.

https://emoji.discourse-cdn.com/twitter/slight_smile.png?v=9Thanks

Anna

.
Level 6
  • .14600775
  • Author
  • Level 6
  • August 27, 2020
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 All the best, Anna
D
Level 1
Forum|alt.badge.img+2

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 youve 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

.
Level 6
  • .14600775
  • Author
  • Level 6
  • August 27, 2020

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

D
Level 1
Forum|alt.badge.img+2

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

Community Code of ConductAccessibility statement