how to aggregate nested average case statement

brave14
brave14 Member
edited January 2023 in Beast Mode

Business task: "what percent of this region's sites beat their target signups YTD"? So I want to take the average daily signup for each site within a region and see if it passes its target. If it does pass, it gets a 1, else 0. After this, I then want to aggregate it to the region level. So for example if 5 out of 6 sites passed, then the passing rate would be 83% for the region.

This is my beast mode so far:

(Case 

  when AVG(`SignUps`) >= AVG(`Target`)

  then 1 

  else 0 

End)

The problem is that while this aggregation does work when I have the site column, it doesn't aggregate when I replace the site column with the region column. Assuming this problem is fixed, I then need to take the sum of all the 1's within a region and divide it by the number of stores so I can get a passing rate for a region. So I thought of how to approach this by using the beast mode below but I still have that first issue in the first part so I was wondering on how I should go about this. Thanks!

SUM(DISTINCT Case 

  when AVG(`SignUps`) >= AVG(`Target`)

  then 1 

  else 0 

End)

/

COUNT(DISTINCT `StoreNumber`)

Tagged:

Answers

  • sum(count(distinct case when AVG( SignUps ) >= AVG(`Target`) then StoreNumber end)) fixed (by Region )

    /

    sum(count(distinct StoreNumber )) FIXED (by Region )


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman