Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
qluser01
Contributor

Calculate % of value in 2019

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.

Labels (3)
13 Replies
Vegar
Honored Contributor

Re: Calculate % of value in 2019

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))
Please ekskuse my Norglish and Swenglish typos.
qluser01
Contributor

Re: Calculate % of value in 2019

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 ',');

test1.JPG

Re: Calculate % of value in 2019

Try this

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

Re: Calculate % of value in 2019

May be this for the other task

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

Re: Calculate % of value in 2019

yes 🙂

qluser01
Contributor

Re: Calculate % of value in 2019

What should I study to master it?

qluser01
Contributor

Re: Calculate % of value in 2019

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?

 

Re: Calculate % of value in 2019

Something like this

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

Re: Calculate % of value in 2019

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 ',');

 

test2.JPG

 

 

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

 

test2.2.JPG

 

 

Any ideas on how to change the script?