2 part question here. The first issue I am having is with creating a table calculation for percent difference from first.
I have created custom bins to get years 0-5. 1 location has that time frame, while the other only has year 0 and 1. When I try to build the table calculation taking the Percent difference from first, and exclude the location detail, it appears to take the null values from years 2-5 for the location that doesn't have any data and the totals for years 2-5 that do exist, and aggregate them together, giving me a weird % difference.
Is it possible to exclude these null values, and make the calculation only pay attention to the values that truly exist?
The second question: With a larger data set, locations have different life spans, and I am attempting to create a simple forecast based on the historical success of other locations percent difference from the first.. Is there a way to build a calculation that would place actuals on display if they exist, else do the calculation of "most recent actual*respective change"? Thus extending the yellow line in the forecasting tab. For example, 5 locations had a median change in 50% from year 0 to year 2, and 60% from year 0 to year 3 based on a set of data. I would like to apply those percent changes to locations that would not have a year 2 or year 3 yet.
Attached is data sample.