2 Replies Latest reply on Aug 31, 2016 6:05 AM by Andrew Watson

    Calculate all values based on a value from specific date

    Bobo Wieland

      To simplify: I have a dataset with dates and quantities of products for each date.

       

      2016-08-01: 500
      2016-08-02: 499
      2016-08-03: 503

      ...
      2016-08-31: 555

       

      I would like to create a date picker to be used as a "base linte date" for all calculations. So if I pick 2016-08-01 I would get the data series:

       

      2016-08-01: 0
      2016-08-02: -1
      2016-08-03: 3

      ...
      2016-08-31: 55

       

      How should I do this?

       

       

      (By the way I explain this you might guess that I have trouble finding the information searching - I'm sorry if this is something rudimentary)

        • 1. Re: Calculate all values based on a value from specific date
          chris.moore.11

          Hi, see the attached.

           

          You could created a parameter called "Choose Baseline" to enter your baseline date.

           

          The calculated field "baseline" is made from that parameter.

          {FIXED : sum(if [Date] = [Choose Baseline] then [Quant] else null end)}

           

          Then the field "difference" is just the difference between the baseline and the value for that date.

          [Quant] - [Baseline]

           

          Hopefully this helps.

          1 of 1 people found this helpful
          • 2. Re: Calculate all values based on a value from specific date
            Andrew Watson

            Your date picker should be a parameter. Create a parameter with a Date data type and allow the relevant range you want the user to select. For this example I'll call the parameter DatePicker

             

            Next create a calculated field to 'fix' the amount to the amount on the selected date, i.e. 500 using your example above

             

            { FIXED : SUM( IF [Date] = [DatePicker] THEN [Amount] END )}

             

            Now you have your amount fixed based on the chosen date now you can use that in another calculation to calculate the difference:

             

            SUM([Amount]) - SUM([AboveFixedCalculation])

            1 of 1 people found this helpful