Case Statement with YTD

Rupak

Hi Folks,

I'm trying to derive this through Beast mode calculation.

Requirement: Case statement
Score 1 - If my error % is 0 - 75%

Score 2 - If my error % is 75.1% - 95%

Score 3 - If my error % is 95.1% - 100% (Includes 100)

Score 4 - If my error % is 100% (YTD)

Finding difficulty in writing a case statement for 'Score 4'.

Date fields are (Month and Year)

Question:
-When a person's error % for March 2023 is 100% but for Jan and Feb its less than 100% then the score should be 3 when filtered to March.
-If the person's error % is 100% for all months YTD then it the score should be 4 (Fixed).

Any help on how to derive this using beast mode please?

The calculation that I tried and didn't work is

MichelleH

@Rupak Instead of using a fixed function, you can add a case statement within your sums to find the current year totals like this:

  when sum(case when `Year` = YEAR(CURRENT_DATE()) then `Count Yes` end) 
      / 
      sum(case when `Year` = YEAR(CURRENT_DATE()) then `Count Overall` end) > 0.99 then 4