How to compare two series that are offset by 2 weeks?

user18945

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

ST_Superman

Try creating a beastmode like this:

 

CASE WHEN `series_field`='red' then DATEADD(`date_field`, INTERVAL 2 WEEK) ELSE `date_field` END

user18945

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.

ST_Superman

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 

user18945

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'.

ST_Superman

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.