5 Replies Latest reply on Mar 19, 2014 1:22 PM by Noah Salvaterra

# Take a single measure value and calculate across other values

I've attached a packaged workbook to show the problem.  Essentially, I have a data source which stores a statistical forecast value (doesnt change) as well as vetted forecast values (experts) throughout the month (which changes).

Week over week, the expert will provide their adjusted forecast for the month.  I want a calculation that measures the difference against the statistical forecast value which was only submitted at the beginning of the month.  The sample table shows the calculated field result that I need help building.  In essence, I want a formula that functions as:

variance from statistical = [date_produced(i).expert.forecast value] - [date_begin.statistical.expert.value]

where [i] is the date_produced as the month goes by.

Is creating this calculation possible?  I've wondered about windowed functions but I can't figure out the semantics to make it work.  I need to create a stacked bar chart for visualization.

I've attached the workbook and the image viz that I'm trying to produce.

• ###### 1. Re: Take a single measure value and calculate across other values

Like this (attached)?

I'll walk through the steps if it looks good, but didn't want to get scooped while I work on a long winded explanation.

Also, do you just need this to function one month at a time, or would you want it to update to the most recent statistical forecast? I mainly went from your picture.

N.

• ###### 2. Re: Take a single measure value and calculate across other values

Noah, this is fantastic.  I do need the function to work one month at a time, but also need it to update with the most recent statistical forecast when it becomes available.  How would that work then there are 2 stat forecasts in the month?

• ###### 3. Re: Take a single measure value and calculate across other values

In that case I'd use something like:

If [Forecast]>0 and [type]='Statistical' then [Forecast] else Previous_Value(0) end

Compute using should be set to date (similar to the example above)

I didn't try that one out, still on v8 at work, so can't open my own file. I think it should be mostly correct, but might require some token aggregation. This would grab the statistical forecast when there is one, and when there isn't it would use the last value of itself (which would still be the applicable forecast). Then in the display you'd want to hide type=statistical since these don't have an expert comparison value and the most recent statistical forecast has been pulled along to the expert forecast rows.

Did you need more explanation on the solution above? I'm happy to provide, but if you've got it, I'm also happy not to provide.

N.

1 of 1 people found this helpful
• ###### 4. Re: Take a single measure value and calculate across other values

Yeah, i was able to reverse engineer the original response and realized I had to hide the 'first' value instead of 'exclude'.  I'll give the that new formula a shot and see if that works if the statistical forecast does change.  I greatly appreciate the help in this!

• ###### 5. Re: Take a single measure value and calculate across other values

No problem, it was a fun question.

N.