Creating a Bar chart for Revenue by Year

[Rich]

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?

Thanks in Advance 

[sergio0592]

Hi,

In your bar chart, just use

as dimension = Decision_Year

as expression=SUM(Decision_Year_Revenue)

[Rich]

Thanks for your Reply,
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 ?

[Channa · Accepted solution]

HI please find the attach APP

Main:
LOAD
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

[Rich]

Hi Channa,

This Works perfectly. Thank you so much 🙂

[Rich]

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  

Thanks in Advance