How to compare two series that are offset by 2 weeks?
I have some data that is offset in time by 2 weeks from another set of data that I want to compare it to. If I graph them together you can see the relationship, but I'd like to shift one of them by 2 weeks to make them align in the graph. Is this possible?
An example might help to make more sense:
We set the price of widgets when we put them out for sale. However, the sales don't show up in the system for 2 weeks. I'd like to line them up so we can see how the price is affecting sales.
Thanks for any help
Comments
-
Try creating a beastmode like this:
CASE WHEN `series_field`='red' then DATEADD(`date_field`, INTERVAL 2 WEEK) ELSE `date_field` END
1 -
Thanks for the reply. I'm a newb at this, so please bear with me.
I'm not clear on what you mean by `series_field`='red'
In my sample graph the red line is the '# of sales' and the blue line is 'price'. I get that I'd use the variable I'm using for the date as the `date_field` I'm just not seeing the differnece between what you're calling 'series_field' and 'red' they seem like they are the same thing (# of sales) to me.
Thanks again for the help. I really appreciate it.
0 -
No worries @user18945
can you attach a screenshot of what your dataset looks like. That will help me write the SQL code for the beast mode calculated field
0 -
Thanks @ST_-Superman-_!
The screenshot is the 3 columns I'm working with, there's a lot more in there. I want to shift `Performance Payout (Base Rate)` 2 weeks later compared to 'Approved Test Cases'.
0 -
Ok, If you need to push the date forward two weeks then I believe you are going to need to "flatten" your data set.
You currently have a date set the looks like this:
Test Cycle Activation Date Approved Test Cases Performance Payout (Base Rate) 2/1/2019 7 5 2/2/2019 10 9 2/3/2019 5 7 2/4/2019 6 1 2/5/2019 3 2 2/6/2019 12 3 2/7/2019 7 5 2/8/2019 9 9 2/9/2019 1 8 2/10/2019 5 5 2/11/2019 6 6 2/12/2019 3 9 2/13/2019 8 7 2/14/2019 3 4 2/15/2019 7 7 2/16/2019 6 3 2/17/2019 7 2 2/18/2019 8 6 2/19/2019 3 9 2/20/2019 9 1 2/21/2019 1 7 However, you will need to "collapse" the columns to look like this:
Date Measure Amount 2/1/2019 Approved Test Cases 7 2/1/2019 Performance Payout (Base Rate) 5 2/2/2019 Approved Test Cases 10 2/2/2019 Performance Payout (Base Rate) 9 2/3/2019 Approved Test Cases 5 2/3/2019 Performance Payout (Base Rate) 7 2/4/2019 Approved Test Cases 6 2/4/2019 Performance Payout (Base Rate) 1 2/5/2019 Approved Test Cases 3 2/5/2019 Performance Payout (Base Rate) 2 2/6/2019 Approved Test Cases 12 2/6/2019 Performance Payout (Base Rate) 3 2/7/2019 Approved Test Cases 7 2/7/2019 Performance Payout (Base Rate) 5 2/8/2019 Approved Test Cases 9 2/8/2019 Performance Payout (Base Rate) 9 2/9/2019 Approved Test Cases 1 2/9/2019 Performance Payout (Base Rate) 8 2/10/2019 Approved Test Cases 5 2/10/2019 Performance Payout (Base Rate) 5 2/11/2019 Approved Test Cases 6
This will allow you to acomplish the date manipulation that you are looking for. In this case it would be a new date field that you would want to calculate. `Comparative Date` could be something like this:
case
when `Measure`='Approved Test Cases'
then DATEADD(`Date`, INTERVAL 2 WEEK)
ELSE `Date`
END
Your graph would then use `Comparative Date` in the x-axis, `Amount` in the y-axis, and `Measure` as the series.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 754 Beast Mode
- 61 App Studio
- 41 Variables
- 693 Automate
- 178 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive