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

user18945
user18945 Member
in Charting

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

  • ST_Superman
    ST_Superman Domo Employee

    Try creating a beastmode like this:

     

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

  • user18945
    user18945 Member

    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.

  • ST_Superman
    ST_Superman Domo Employee

    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
    user18945 Member

    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
    ST_Superman Domo Employee

    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)
    2/1/201975
    2/2/2019109
    2/3/201957
    2/4/201961
    2/5/201932
    2/6/2019123
    2/7/201975
    2/8/201999
    2/9/201918
    2/10/201955
    2/11/201966
    2/12/201939
    2/13/201987
    2/14/201934
    2/15/201977
    2/16/201963
    2/17/201972
    2/18/201986
    2/19/201939
    2/20/201991
    2/21/201917

     

     

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

    DateMeasureAmount
    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

    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.

This discussion has been closed.

Categories