I have a field {verification#} which is a unique code for each report. I also have the follow formulas:
Not ef=ifelse({non_compliance} = 1 {verification#},NULL)
Ef=ifelse({compliance} = 1,{verification#},NULL)
OpforIM=distinct_count({Not Ef})/(distinct_count({Not Ef})+distinct_count({Ef})).
We also have a field {structure_level_2} which shows the regions and {site} which shows a site.
How do I create a measure that calculates whether a site through OpforIM percentage is greater than the regional (structure_level_2} 95th percentile.
Lets break this usecase into steps:
step 1: to calculate OpforIM at site level, we need to calculate the nested functions at site level.
e.g. distinctCountOver({Not Ef},{site}, pre_agg)/(distinctCountOver({Not Ef},{site},pre_Agg)+distinctCountOver({Ef},{Site},pre_agg))
step 2: write similar relevant function to determine regional value
step 3: use percentile function to fetch 95th percentile
step 4: write an if else function to check the greater than condition
The outline may vary based on data granularity and the formula may need syntax check for recreation.
Will you be able to share snippet of sample output you are looking for?
Hi @Roger123,
It’s been awhile since we last heard from you. Did you have any additional questions regarding your initial topic or were you able to find a work around?
If we do not hear back from you within the next 3 business days, I’ll go ahead and close out this topic.
Hi @Roger123,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for any relevant information that may be needed.