Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Rich
Contributor
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. 

IdDecision Year Decision Year RevenueYear +1 RevenueYear + 2 Revenue
12016100
22016223.723.7
32016519.219.2
42017312.6512.65
52017405757
62017000
72017333
82018333
92018225555
1020182326.626.6
112018121212
12201910.6750.675
1320201100
1420201212.812.8
1520211317.0517.05
162021322

 

The Bar Chart needs to look somewhat like this: 

112.PNG

What's the best way to approach this?

 

Thanks in Advance 

Labels (4)
1 Solution

Accepted Solutions
Channa
Specialist III
Specialist III

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

Channa

View solution in original post

5 Replies
sergio0592
Specialist III
Specialist III

Hi,

In your bar chart, just use

as dimension = Decision_Year

as expression=SUM(Decision_Year_Revenue)

Rich
Contributor
Contributor
Author

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
Specialist III
Specialist III

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

Channa
Rich
Contributor
Contributor
Author

Hi Channa,

This Works perfectly. Thank you so much 🙂
Rich
Contributor
Contributor
Author

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