Use dimension value in expression and as dimension

curiousfellow — Fri, 29 Mar 2019 14:35:55 GMT

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.

topic Use dimension value in expression and as dimension in QlikView

Use dimension value in expression and as dimension