Beast Mode

Beast Mode

Calculating Differences based on the Value of a Column

Member
edited February 2024 in Beast Mode

I'm trying to solve a problem where I want to compare the average of two columns based on the value of another column. A simplified version of my data table looks something like this:

Score A | Score B | Score C | Survey Type

10 | 20 |19 | pre

14 | 26 |23 | post

7 | 6 |8 | pre

10 | 10 |12 | post

I want to calculate the percentage change for each score column (A, B, C) for "pre" surveys vs. "post" surveys based on the Survey Type column.

I already have the averages of each score/survey type calculated using beast modes and filters based on the survey type, but I don't have a way to calculate the difference.

Would this be something I could do with ranking and windowing in the ETL?

Tagged:

Best Answer

  • edited February 2024 Answer ✓

    Here is what the beastmode would look like for column A % change between pre and post:

    (AVG(CASE WHEN Survey Type = 'post' THEN Score A END) -
    AVG(CASE WHEN Survey Type = 'pre' THEN Score A END)) /
    AVG(CASE WHEN Survey Type = 'pre' THEN Score A END)

    You can copy and paste this for Score B and C - just replace Score A with Score B and Score C respectively.

    If I solved your problem, please select "yes" above

Answers

  • edited February 2024 Answer ✓

    Here is what the beastmode would look like for column A % change between pre and post:

    (AVG(CASE WHEN Survey Type = 'post' THEN Score A END) -
    AVG(CASE WHEN Survey Type = 'pre' THEN Score A END)) /
    AVG(CASE WHEN Survey Type = 'pre' THEN Score A END)

    You can copy and paste this for Score B and C - just replace Score A with Score B and Score C respectively.

    If I solved your problem, please select "yes" above

  • Thanks for your help with this, @ColemenWilson I was making the mistake of not averaging each case statement individually. I'll give this a shot and let you know how it goes.

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In