# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Contributor

## Calculate % of value in 2019

Hi all!

I have a table like:

year | amount

2019 | 2000

2018 | 1800

2017 | 1600

2016 | 1500

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)

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)

Labels (3)

• ### Script

13 Replies Partner

## Re: Calculate % of value in 2019

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

(Sum({<Year={\$(= only(Year))}>}amount)
-
Sum({<Year={\$(= max({<Year>}Year))}>}amount))
/
Sum({<Year={\$(= max({<Year>}Year))}>}amount))
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
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]:
[year] AS [Year],
[amount];
[
year,amount
2019,1600
2018,1500
2017,1400
2016,1150
2015,1000
2014,700
](delimiter is ',');```  MVP

## Re: Calculate % of value in 2019

Try this

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

## 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)```
Contributor

yes 🙂

Contributor

## Re: Calculate % of value in 2019

What should I study to master it?

Contributor

## Re: Calculate % of value in 2019

TOTAL is not OK if I add one more dimension

```[table1]:
[year] AS [Year],
[amount];
[
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? MVP

## Re: Calculate % of value in 2019

Something like this

```=(Sum(amount) - Sum(TOTAL <country> {<Year = {2019}>} amount))
/
Sum(TOTAL <country> {<Year = {2019}>} amount)```
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]:
country,
[year] AS [Year],
[amount];
[
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?