Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Rich
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. 

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 

1 Solution

Accepted Solutions
Channa
Contributor III

Re: Creating a Bar chart for Revenue by Year

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
5 Replies
sergio0592
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)

Rich
New Contributor

Re: Creating a Bar chart for Revenue by Year

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

Re: Creating a Bar chart for Revenue by Year

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
New Contributor

Re: Creating a Bar chart for Revenue by Year

Hi Channa,

This Works perfectly. Thank you so much 🙂
Rich
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  

Thanks in Advance