Calculate % of value in 2019

[qluser01]

Hi all!

I have a table like:

year | amount

2019 | 2000

2018 | 1800

2017 | 1600

2016 | 1500

Task 1.

I need to prepare a bar chart where on x axis will be years (from 2019 to 2016)

and on y axis will be % of decrease in amount by a formula:

(amount(of current year) - amount (2019) )/ amount (2019)

Please help me to write write a script.

Task 2.

Advanced version of task 1. In case my selections leave only years from 2018 to 2016 (the will be no 2019 year) - the formula should find the max year and use it instead of srictly saying 2019:

(amount(of current year) - amount ( max(year) 2019) )/ amount ( max(year) 2019)

Please help me to write write a script.

[Vegar]

Your $ expansions are always calculated "outside" of your chart and you should consider them as the same static value for all dimensions. 

In your first chart your $(= max(Year)) = 2019. UK will not show because the UK have no 2019 figures and your will be dividing by zero. When selecting UK your $(= max(Year)) = 2018 and thereby you will get values for the UK.

[sunny_talwar]

In this case, you can try this

=(Sum(amount) - Sum(TOTAL <country> Aggr(If(Year = Max(TOTAL <country> Year), amount), Year, country)))
/
Sum(TOTAL <country> Aggr(If(Year = Max(TOTAL <country> Year), amount), Year, country))

[qluser01]

That's impressive.

Last question: how to change the script to work if we have several items for each year/county (different amounts which we first need to make Average for current script to work)?

And Sum will not work as we need to deal with average amount for each year/county.

In example below, average amount for UK, 2018  should be (1500+1400+1700)/3 = 1533.3

Is it possible not to create a new table with average figures and then use current version of script, but to amend the script to fit for new data?

[table1]:
LOAD
	country,
	[year] AS [Year],
	[amount];
LOAD * INLINE 
[
country, year,amount
UK, 2018,1500
UK, 2018,1400
UK, 2018,1700
UK, 2017,1400
UK, 2017,1800
UK, 2017,1100
UK, 2017,1000
UK, 2015,1000
UK, 2014,700
UK, 2014,600
UK, 2014,650
USA, 2019,3600
USA, 2019,3700
USA, 2019,3800
USA, 2018,3500
USA, 2018,3300
USA, 2018,3800
USA, 2017,3400
USA, 2017,3450
USA, 2017,2800
USA, 2016,3150
USA, 2016,3250
USA, 2015,3000
USA, 2014,2700
USA, 2014,2800
USA, 2014,2850
](delimiter is ',');

[sunny_talwar]

I am not entirely sure I understand the output you are hoping to get. Would you be able to share the expected output based on the input provided?