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


  • Try creating a beastmode like this:


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

    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • Thanks for the reply. I'm a newb at this, so please bear with me. Smiley Happy


    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.

  • 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 

    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • 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'.

  • 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 DateApproved Test CasesPerformance Payout (Base Rate)



    However, you will need to "collapse" the columns to look like this:

    2/1/2019Approved Test Cases7
    2/1/2019Performance Payout (Base Rate)5
    2/2/2019Approved Test Cases10
    2/2/2019Performance Payout (Base Rate)9
    2/3/2019Approved Test Cases5
    2/3/2019Performance Payout (Base Rate)7
    2/4/2019Approved Test Cases6
    2/4/2019Performance Payout (Base Rate)1
    2/5/2019Approved Test Cases3
    2/5/2019Performance Payout (Base Rate)2
    2/6/2019Approved Test Cases12
    2/6/2019Performance Payout (Base Rate)3
    2/7/2019Approved Test Cases7
    2/7/2019Performance Payout (Base Rate)5
    2/8/2019Approved Test Cases9
    2/8/2019Performance Payout (Base Rate)9
    2/9/2019Approved Test Cases1
    2/9/2019Performance Payout (Base Rate)8
    2/10/2019Approved Test Cases5
    2/10/2019Performance Payout (Base Rate)5
    2/11/2019Approved Test Cases



    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:


    when `Measure`='Approved Test Cases'

    then DATEADD(`Date`, INTERVAL 2 WEEK)

    ELSE `Date`



    Your graph would then use `Comparative Date` in the x-axis, `Amount` in the y-axis, and `Measure` as the series.

    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
This discussion has been closed.