1 Reply Latest reply on Jul 8, 2016 7:02 PM by swaroop.gantela

    Set Range Based on Dynamic Date (External) Per Customer ID

    Brendan Ham

      Hello All,

       

      I'm looking to create a view that allows me to monitor growth on a set range average (8 weeks prior & following) from a specific date of engagement by customer.

       

      Baked down, I have the below variables I am hoping I can get some advice on tying together:

       

      • External data (Excel)
        • Customer ID (Same as internal Customer ID)
        • Timestamp: Date of engagement

       

      • Internal data
        • Net Revenue
        • Customer ID (Same as external Customer ID)
        • Order Date

       

      I am looking to set the Timestamp as a start point along the X axis, breakout Customer ID's on the Y, and give two separate bars per customer:

      1. Prior 8 week average (Net Rev)

      2. Following 8 week average (Net Rev)

       

      However, I am having an issue tying the Net Revenue to these 8 week average time frames stemming from the dynamic Timestamp data (external).

       

      I have tried creating a calculated field tying the revenue to these dates so that I can break out the separate values, but have not been having luck in doing so.

       

      Various formulas to tie these together have not work, one for example being:

      WINDOW_SUM(SUM([Net Revenue]),[Timestamp],,6)

      WINDOW_SUM(SUM([Net Revenue]),(Source_File.[Timestamp]),,6)

       

      The external Timestamp format is in the format: 7/8/2016  10:10:58 AM

       

      Would love to hear any advice out there on how to compare these net before/after averages and am sure that it can translate to some very useful A/B testing views. Does anyone have a similar view they have worked out or tips for working through this one?

       

      Cheers,

       

      Brendan

        • 1. Re: Set Range Based on Dynamic Date (External) Per Customer ID
          swaroop.gantela

          Brendan,

           

          I apologize at the outset for just taking a surface stab at it,

          but please see if the attached could be a first step.

           

          I was able to calculate sums on prior8 and following8 and

          get them two window sum between the sources.

           

          One major problem is that I made the assumption that each

          Customer has only one engagement date.

           

          If that is not the case, will revisit.

           

          Also, not crazy about the dual axis bars, one on top of the other,

          having to use the width to differentiate. Also will be problematic

          if Customers are on the Row shelf. I probably didn't catch your

          vision for the graph, maybe if you could post a mockup desired

          view.

           

          [Following8]:

          IF ATTR([Order Date])<DATEADD('week',8,ATTR([Sheet1 (210749stampA)].[Timestamp]))

          AND ATTR([Order Date])>=ATTR([Sheet1 (210749stampA)].[Timestamp])

          THEN SUM([Revenue])

          END

           

          [FollowingSum]:

          IF FIRST()=0 THEN

          WINDOW_SUM([Following8])

          END