CY-PY calculation for a value within a column

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.
Answers
-
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
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 38 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 258 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 104 Apps
- 714 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 26 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 653 ひらめき共有