# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
New Contributor

## Creating a Bar chart for Revenue by Year

Hi Everyone,

So, I have the below data set and I am trying to plot a bar chart showing the Total Revenue for Each year.

 Id Decision Year Decision Year Revenue Year +1 Revenue Year + 2 Revenue 1 2016 1 0 0 2 2016 2 23.7 23.7 3 2016 5 19.2 19.2 4 2017 3 12.65 12.65 5 2017 40 57 57 6 2017 0 0 0 7 2017 3 3 3 8 2018 3 3 3 9 2018 22 55 55 10 2018 23 26.6 26.6 11 2018 12 12 12 12 2019 1 0.675 0.675 13 2020 11 0 0 14 2020 12 12.8 12.8 15 2021 13 17.05 17.05 16 2021 3 2 2

The Bar Chart needs to look somewhat like this: What's the best way to approach this?

Labels (5)

• ### logic

1 Solution

Accepted Solutions
Valued Contributor III

## Re: Creating a Bar chart for Revenue by Year

HI please find the attach APP

Main:
Id,
"Decision Year " as Year,
"Decision Year Revenue" as Year_r,
"Year +1 Revenue" as Year_1,
"Year + 2 Revenue" as Year_2
FROM [lib://TEST/year.xlsx]
(ooxml, embedded labels, table is Sheet1);
load Year as Byear,sum(Year_r) as Byear_r,sum(Year_1) as BYear_1,sum(Year_2) as Byear_2 Resident Main group by Year;

expression:=Sum(Byear_r)+Above(BYear_1)+above(Above(Byear_2))

or

=IF(IsNull( above(Above(Byear_2))),Sum(Byear_r)+Above(BYear_1), Sum(Byear_r)+Above(BYear_1)+above(Above(Byear_2)))

it will work for you

Channa
5 Replies
Valued Contributor II

## Re: Creating a Bar chart for Revenue by Year

Hi,

In your bar chart, just use

as dimension = Decision_Year

as expression=SUM(Decision_Year_Revenue)

New Contributor

## Re: Creating a Bar chart for Revenue by Year

But This doesn't exactly give me the correct value as required.

For example, with your above method The Revenue for 2021 will be (13+3)= 26
But the value of Revenue for 2021 also needs to be influenced by the columns Year+1 and Year +2 Revenue for the years 2020 and 2019 respectively. which should sum up to (13+3+12.8+0+0.65)

How do I achieve this using expressions or logic in Qlik ?
Valued Contributor III

## Re: Creating a Bar chart for Revenue by Year

HI please find the attach APP

Main:
Id,
"Decision Year " as Year,
"Decision Year Revenue" as Year_r,
"Year +1 Revenue" as Year_1,
"Year + 2 Revenue" as Year_2
FROM [lib://TEST/year.xlsx]
(ooxml, embedded labels, table is Sheet1);
load Year as Byear,sum(Year_r) as Byear_r,sum(Year_1) as BYear_1,sum(Year_2) as Byear_2 Resident Main group by Year;

expression:=Sum(Byear_r)+Above(BYear_1)+above(Above(Byear_2))

or

=IF(IsNull( above(Above(Byear_2))),Sum(Byear_r)+Above(BYear_1), Sum(Byear_r)+Above(BYear_1)+above(Above(Byear_2)))

it will work for you

Channa
New Contributor

## Re: Creating a Bar chart for Revenue by Year

Hi Channa,

This Works perfectly. Thank you so much 🙂
New Contributor

## Re: Creating a Bar chart for Revenue by Year

Added complexity. Say i have a few other columns in the same sheet. Like Owner and Segment

How do i use filters to filter the data based on user selection ? I would like to have the user select a particular owner or segment and the chart to reflect those values dynamically.

The Above chart is currently static based on the logic used during Load