Calculate the variation from a field name

[#1]

Hi I have created an iterative table like tis one:

Tab_Year:

LOAD

$(year) as Year,

Region,

AK as Price,

AS as Quantity

Resident Tab_year_tmp;

.....

//Second year

LOAD

($(year)+'1') as Year,

Region,

AL as Price,

AT as Quantity

Resident Tab_year_tmp;

.....

....

where $(year) is the variable of the starting year, AK is the 2014 price and AS the quantity of 2014, AL is the price of 2015, AT the quantity of 2015 and so on.

now I'd like to do something like that:

(Price2015-Price2014)/Price2014

for each year but I can't figure it out because the price of the different years are all in the field Price.

(I should have for each year the actual value and the previous one)

how can I do???

Thanks in advance!

[JonnyPoole]

SET ANALYSIS will help. You can use it in a text box, chart, anywhere in the UI where expressions are allowed:

(sum ( {$<Year={2015}>}  Price) -  sum ( {$<Year={2014}>}  Price) - ) /  sum ( {$<Year={2014}>}  Price)

[JonnyPoole]

Was just thinking that to create a more dynamic solution try this:

Add 'Year' as your chart dimension.

Sort the chart on Year ascending

add an expression that grabs the preceding row using the above() function to calculate % change:

(sum(Sales) - above(sum (Sales))) / above (sum (Sales))

[#4]

But I can't write 2014..2015 in the expression because I have the $(year) that counts the years.

[#5]

I tried with this expression but It doesen't show any value!

((sum({$<Year={$($(#year)}>}(Price/Quantity))-sum({$<Year={$(#=Only($year)-1)}>}(Price/Quantity)))/(sum({$<Year={$(#=Only($year)-1)}>}(Price/Quantity))))