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

Calculate all values based on a value from specific date

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

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

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