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
- All Categories
- 1.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 278 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.7K Transform
- 89 SQL DataFlows
- 557 Datasets
- 2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 571 Beast Mode
- 11 App Studio
- 28 Variables
- 579 Automate
- 141 Apps
- 414 APIs & Domo Developer
- 23 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 352 Distribute
- 92 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 92 Manage
- 89 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 88 Community Announcements
- 4.8K Archive