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

JS0676

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

DavidChurchman

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

marcel_luthi

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.

DavidChurchman

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

JS0676

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

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.