Calculate Variance of a variable from two date ranges

user16945
user16945 Member
in Magic ETL

Hello I am fairly new to domo, and I am wondering how to calculate the variance of a variable in my data set from a specific time frame (Quarter 1 2019) and divide that by then variance from another time frame (Quarter 3 2018)?

 

So far I have tried this, but this did not match with the values from a calculation in Excel:

VAR_POP(CASE WHEN `deploy_date` >= '1/1/2019' and `deploy_date` <= '3/31/2019' THEN `delivered` ELSE 0 end) /

VAR_POP(CASE WHEN `deploy_date` >= '7/1/2018' and `deploy_date` <= '9/30/2018' THEN `delivered` ELSE 0 end)

 

and

 

VAR_POP((CASE
WHEN `deploy_date` >= '1/1/2019' and `deploy_date` <= '3/31/2019' THEN VAR_POP(`delivered`)
Else 0 end)) /
VAR_POP((CASE
WHEN `deploy_date` >= '7/1/2018' and `deploy_date` <= '9/30/2018' THEN VAR_POP(`delivered`)
Else 0 end))

Best Answer

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    Are you sure that you're looking for the population variance?

    1.png

    I also don't think that you would want to use the else 0 in your case statement because that will increase the N value.

     

    You should try:

    VAR_POP(CASE WHEN `deploy_date` >= '1/1/2019' and `deploy_date` <= '3/31/2019' THEN `delivered`  end)
     /
    VAR_POP(CASE WHEN `deploy_date` >= '7/1/2018' and `deploy_date` <= '9/30/2018' THEN `delivered`  end)

Answers

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    Are you sure that you're looking for the population variance?

    1.png

    I also don't think that you would want to use the else 0 in your case statement because that will increase the N value.

     

    You should try:

    VAR_POP(CASE WHEN `deploy_date` >= '1/1/2019' and `deploy_date` <= '3/31/2019' THEN `delivered`  end)
     /
    VAR_POP(CASE WHEN `deploy_date` >= '7/1/2018' and `deploy_date` <= '9/30/2018' THEN `delivered`  end)
  • user16945
    user16945 Member

    Thank you, I will try this now

Categories