May be using this:
=If(IsNull(revenue), RangeAvg(Above([Exp Name], 1, 3)), revenue)
This would have issue when you make selections. Would you be able to elaborate on what this should look like after a selection is made in date field?
Community_214423.qvw 152.5 K
Thank you for your answer!
If the value of say 2015-02 was way lower/higher than normal I would like to permit for the possibility of de-selecting that date-value pair and have the forecast calculated from the values of 2015-12, 2016-01 & 2016-03 instead. Or if you would want to show the forecast as it was 2015-11 you should be able to de-select all dates later than 2015-11 and have 2015-12 and the rest forecasted instead.
I don't understand how you get the [Exp Name] table. When I use your script but with [revenue] instead of [Exp Name] as measurement in the line chart the first forecasted value becomes correct. The next forecasted value is the average of the 2 last actual values and the last forecasted value is the same as the last actual value (only 3 values are calculated). Can you explain how to build the [Exp Name] table?
I think I'm almost there but the behavior is weird.
I have now created this following variable that I use as measurement in the line chart as $(vFC):
set vFC = If(isnull(revenue), RangeAvg(Above($(vFC), 1, 3)), revenue);
This however doesn't show any data unless I change it to:
set vFC = If(isnull(revenue), RangeAvg(Above('$(vFC)', 1, 3)), revenue); //notice the '' around $(vFC)
When I load the data with the latter variable definition it shows the data but no forecasted values and here comes the weird part:
When I now change the variable to the first definition (after loading the data once with the latter) three (3) forecasted (and correct) values suddenly appears alongside the actual ones.When I load the data a second time three more (6) forecasted (and slightly incorrect) values are shown. And when I load the data again three more (9) forecasted (and slightly more off) values are shown and the three previously created forecasted values gets corrected. For each time I reload the data with the load button three more forecasted values are shown. The later forecasted values that are created after the first load are however slightly off but gets closer and closer to being correct (the average of the three previous values) the more times I reload the data.
Can anyone explain what is going on here??? And more importantly, can anyone solve this and make is work as intended?