How do I create a MoM variance formula when the date format and interval varies for all entries?

JS0676
JS0676 Member
in Beast Mode

Here is a sample data, where F11 is for month of November, F10 is for month of October-

image.png

Best Answers

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    I would start by standardizing the date variable. You could use str_to_date:

    CASE

    WHEN left(`Date`,1) = 'F' then str_to_date( concat(`Date`,2023), 'F%c%Y')

    ELSE str_to_date( Date, '%d-%b-%y')

    END

    Here's an article on different date transforms: https://domo-support.domo.com/s/article/360042925494?language=en_US

    And the different codes for date formats: https://domo-support.domo.com/s/article/360043429953?language=en_US

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    Can you describe in plain words what you expect the above formula to do? At a Row level the MAX(Col) of something is always going to be the same value as Col making your denominator to always be 0, causing an error and therefore displaying no data.

    To fix this you'll need to have some sort of aggregation function around your values, providing a table or image that illustrates what the desired final outcome would be is often helpful. If is just a single value for the current month this might be achievable (or the latest month), but if you want a table that shows this on a monthly basis, it's likely you might want to leverage Magic ETL instead.

Answers

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    I would start by standardizing the date variable. You could use str_to_date:

    CASE

    WHEN left(`Date`,1) = 'F' then str_to_date( concat(`Date`,2023), 'F%c%Y')

    ELSE str_to_date( Date, '%d-%b-%y')

    END

    Here's an article on different date transforms: https://domo-support.domo.com/s/article/360042925494?language=en_US

    And the different codes for date formats: https://domo-support.domo.com/s/article/360043429953?language=en_US

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • JS0676
    JS0676 Member

    I standardized the date and next I tried this formula to give variance but it's not giving me any value. Any idea what I might be doing wrong here?

    CASE
    when Account = 'Sales' then
    ((CASE WHEN MONTH(Date) = MAX(MONTH(Date)) THEN SUM(Sales) END) -
    (CASE WHEN MONTH(Date) = MAX(MONTH(Date))-1 THEN SUM(Sales) END))
    /
    (CASE WHEN MONTH(Date) = MAX(MONTH(Date))-1 THEN SUM(Sales) END)
    else 0
    END

    Appreciate your help, thanks!

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    Can you describe in plain words what you expect the above formula to do? At a Row level the MAX(Col) of something is always going to be the same value as Col making your denominator to always be 0, causing an error and therefore displaying no data.

    To fix this you'll need to have some sort of aggregation function around your values, providing a table or image that illustrates what the desired final outcome would be is often helpful. If is just a single value for the current month this might be achievable (or the latest month), but if you want a table that shows this on a monthly basis, it's likely you might want to leverage Magic ETL instead.

Categories