How to calculate a value over a base year in a table

[simotrab]

Hi community,

I got some data like these

load*inline[
year, type, value
2014,a,100
2014,b,100
2015,a,120
2015,b,110
2016,a,90
2016,b,90
];

Now, I need to get a table with this result:

year	value	on_2014
2014	100	100%
2015	120	20%
2016	90	-10%

I.E. I need to calculate the percentage of variation for the type "a" based on a year.

I've tried something like:

sum({$<type = {"a"}>})/sum({$<type = {"a"}, year = 2016>})

But it does not work, so I tried messing up with rangesum() or above(), but I do not getting any proper result.

Thanks in advance

[ajaykakkar93]

hi,

I was able to achieve values like this

 

[ajaykakkar93]

Here is my Code:

Table:
load * inline
[
id,year,type,value
1,2014,a,90
2,2014,a,10
3,2014,b,100
4,2015,a,120
5,2015,b,110
6,2016,a,90
7,2016,b,90
];

FinalTable:
load *,
peek(value,0) as peekVal;
Load
year,
type,
sum(value) as value
Resident Table
group by year,type;

drop Table Table;

[formosasol]

Hi,
What are you trying to calculate, i don't understand your question

Regards
Frank

[jonathandienst]

Here is the syntax correct expression:

sum({$<type = {'a'}>} value) / sum({$<type = {'a'}, year = {2014}>} value)

or

1 - sum({$<type = {'a'}>} value) / sum({$<type = {'a'}, year = {2014}>} value)

to achieve the output described. 

[simotrab]

Hi, thanks for your answer, but now it does not work (neither one nor two, maybe I'm using them wrongly):

[simotrab]

Hi, thanks for your answer, but I'd like to do not work on the script to reach the result. Thanks.

[simotrab]

Hi, I'd like to calculate the % of variation based on the 2014 year. This mean that if in 2014 I've done 100, in 2015 110, it means I got an increase of 10% (110/100-1). If in 2016 I've 90 value, it means I got a 10% decrease (90/100-1). So I forget a 1 in my formula and I wrongly write 2016 instead of 2014 but I did not found how to edit the question.