I want to make a forecast based on sales and prospects previous three years and the prospects of coming year.
year is a field in my table
I my chart I want to show the sales of the last three years and the forecasted year in columns
I have variables for years , v_Year,v_Year-1,v_Year-2, v_Year-3
In my expression I have :
If(year < $(v_Year), sum(sales),
sum({year=$(v_year)}prospect)
/(sum({year=$(v_year-1)}prospect)+ sum({year=$(v_year-2)}prospect)+sum({year=$(v_year-3)}prospect)
*(sum({year=$(v_year-1)}sales)+ sum({year=$(v_year-2)}sales)+sum({year=$(v_year-3)}sales)
)
However I do not get any values for the coming year. Can I use the year value as a dimension and in the expression at the same time ?
I know that in this example the expression could be simplified, but because I have more conditions in the original expression I have to do it this way.
