3 Replies Latest reply: Apr 8, 2017 6:57 AM by Sunny Talwar RSS

    Calculate Sum of X on a date and Y on a date N days earlier

    Matthew Lucas

      I'm trying to create a chart that shows a bar for Sum(x) on a date, a bar for Sum(y) on (date-3), and date is the dimension.

       

      So for example, how could I produce a chart that has the following dimensions/measures:

      1/5/20171/6/20171/7/20171/8/20171/9/2017
      Sum(x) on 1/5Sum(x) on 1/6Sum(x) on 1/7Sum(x) on 1/8Sum(x) on 1/9
      Sum(y) on 1/2Sum(y) on 1/3Sum(y) on 1/4Sum(y) on 1/5Sum(y) on 1/6

       

      The source data is one table with a bunch of different dimensions that the user should be able to filter by:

      DateDimension 1Dimension 2Dimension Nxy
      1/1/2017AC...12
      1/2/2017BD...34
      ...............

       

      My first thought was to use Set Analysis, but after hours of searching, I'm not sure if this is possible.

      Does anyone know of any way to accomplish this using any method?

       

      Basically I'd want something like this:

      Sum(x)

      Sum({<Date={Date-3}>}y)

       

      So that the bar of Sum(y) from the date 1/2/2017 is shown above the date 1/5/2017.

       

      Please let me know if you need any additional details.

      Thanks!