how to aggregate an average case statement

brave14

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`)

GrantSmith

You won't be able to aggregate on an aggregate within a beast mode. You'd need to pre-aggregate your data to get the average signups and average targets. You can then use that aggregated data and apply the beast mode to count the number of records which meet that criteria.

GrantSmith

You won't be able to aggregate on an aggregate within a beast mode. You'd need to pre-aggregate your data to get the average signups and average targets. You can then use that aggregated data and apply the beast mode to count the number of records which meet that criteria.

brave14

@GrantSmith I see... so would I need to pre-aggregate this in either magic ETL or SQL dataflow?