CY-PY calculation for a value within a column

Golfoholic

The issue I am running in to is how to call out the value with in the column i want to find CY-PY variance. I am using the formula from Domo page on the topic. In that formula it only has use using a single column. How can I find that variance of one single value with in a column?

SUM(CASE WHEN YEAR(`Activity_Date`) = YEAR(CURRENT_DATE()) THEN `Value` = 'Occ % of Total Area' ELSE 0 END)

- SUM(CASE WHEN YEAR(`Activity_Date`) = YEAR(CURRENT_DATE()) - 1 THEN `Value` = 'Occ % of Total Area' ELSE 0 END)

This is using aggregate data. So I have end of month data historically, with current day. What I would want to is have a calculation I can use for multiple values all located in the value column. YOY - current day % change over same month LY. MTD. and Today's % changed compared to 2 months ago....April 14th compared to february. Attached is sample data.

Sample Data - Variance Calc help.xlsx

GrantSmith

Hi @user048760

What you're wanting to do is possible by utilizing a date offset dimension. Essentially you'll have records for each day for current, lat week, last month, 2 months ago, whatever you want to define your offset / period definitions. There's been several write ups about this specific topic on the Dojo before. I've written on this topic here: https://dojo.domo.com/discussion/comment/50540#Comment_50540

Short version:

• Create a list of dates (use the dates Domo dimension dataset) with offsets (I used a MySQL DataFlow )
• Join your dataset to this offset table on the date field in your dataset and the comparison date in the offset dataset
• Use the report date in your reports and filter the offset period type (last week, 2 months ago etc)

This will allow you compare a single date to whatever time ago you define. You can use beast modes to define your differences over the different periods. For example -

YoY

SUM(CASE WHEN `Offset` = 'Current' THEN `Value` ELSE 0 END) - SUM(CASE WHEN `Offset` = 'Last Year' THEN `Value` ELSE 0 END)

MoM

SUM(CASE WHEN `Offset` = 'Current' THEN `Value` ELSE 0 END) - SUM(CASE WHEN `Offset` = 'Last Month' THEN `Value` ELSE 0 END)

@jaeW_at_Onyx has a good video of this process as well: https://www.youtube.com/watch?v=CDKNOmKClms