Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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))
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 ',');
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?