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]

Task 1
(Sum({<Year={$(= only(Year))}>}amount)
-
Sum({<Year={2019}>}amount))
/
Sum({<Year={2019}>}amount))

Task 2
(Sum({<Year={$(= only(Year))}>}amount)
-
Sum({<Year={$(= max({<Year>}Year))}>}amount))
/
Sum({<Year={$(= max({<Year>}Year))}>}amount))

[qluser01]

Does not work as expected:

I need to see all bars at the same time (for all years), but task 1 script shows only 2019:

=(Sum({<Year={$(= only(Year))}>}amount) - Sum({<Year={2019}>}amount)) / Sum({<Year={2019}>}amount)

[table1]:
LOAD
	[year] AS [Year],
	[amount];
LOAD * INLINE 
[
year,amount
2019,1600
2018,1500
2017,1400
2016,1150
2015,1000
2014,700
](delimiter is ',');

[sunny_talwar]

Try this

=(Sum(amount) - Sum(TOTAL {<Year = {2019}>} amount))
/
Sum(TOTAL {<Year = {2019}>} amount)

[sunny_talwar]

May be this for the other task

=(Sum(amount) - Sum(TOTAL {<Year = {$(=Max(Year))}>} amount))
/
Sum(TOTAL {<Year = {$(=Max(Year))}>} amount)

[qluser01]

yes 🙂

[qluser01]

What should I study to master it?

[qluser01]

TOTAL is not OK if I add one more dimension

[table1]:
LOAD
country,
	[year] AS [Year],
	[amount];
LOAD * INLINE 
[
country, year,amount
UK, 2019,1600
UK, 2018,1500
UK, 2017,1400
UK, 2016,1150
UK, 2015,1000
UK, 2014,700
USA, 2019,3600
USA, 2018,3500
USA, 2017,3400
USA, 2016,3150
USA, 2015,3000
USA, 2014,2700


](delimiter is ',');

How to change the script  to behave correclty in this case?

[sunny_talwar]

Something like this

=(Sum(amount) - Sum(TOTAL <country> {<Year = {2019}>} amount))
/
Sum(TOTAL <country> {<Year = {2019}>} amount)

[qluser01]

Thnaks! That's better, but this script does not show UK on chart if some years are missing for UK (2019 and 2016 in the example below):

[table1]:
LOAD
	country,
	[year] AS [Year],
	[amount];
LOAD * INLINE 
[
country, year,amount
UK, 2018,1500
UK, 2017,1400
UK, 2015,1000
UK, 2014,700
USA, 2019,3600
USA, 2018,3500
USA, 2017,3400
USA, 2016,3150
USA, 2015,3000
USA, 2014,2700
](delimiter is ',');

bu if we select (via filter component) UK only - it will show it:

Any ideas on how to change the script?