topic Re: Calculate the variation from a field name in QlikView

Calculate the variation from a field name

Mon, 15 Sep 2014 15:09:36 GMT

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!

Re: Calculate the variation from a field name

JonnyPoole — Mon, 15 Sep 2014 15:12:11 GMT

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)

Re: Calculate the variation from a field name

JonnyPoole — Mon, 15 Sep 2014 15:16:13 GMT

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))

Re: Calculate the variation from a field name

Mon, 15 Sep 2014 15:50:49 GMT

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

Re: Calculate the variation from a field name

Tue, 16 Sep 2014 08:26:36 GMT

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))))