Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

Qlik Sense Waterfall Chart (No Extension)

Partner
Partner

Qlik Sense Waterfall Chart (No Extension)

Nowadays, one of the most popular charts used in Finance is a Waterfall chart. Waterfall charts are most suitable in showing how we have arrived at a net value. Because it can clearly break down the cumulative effect of positive and negative contributions. This is very helpful for many different scenarios, from visualizing financial statements to navigating data about population, births and deaths.

It is easy to come up with a waterfall chart in Qlik View. Unfortunately we don’t have a direct way of achieving it in Qlik Sense. Because we don’t have an offset option in bar chart. Anyway it is not impossible to make a waterfall chart in Qlik Sense.  There are few workarounds for this. Here I am giving out one way of achieving it.

Please go through the attached QVF files.

  1. Static Waterfall (This will be easy to follow.  See this first)
  2. Dynamic Waterfall (Practical approach)

While going through this, newbies can also understand Above(), Rangesum(), Rangemin() functions.

Please give it some time to understand how we have achieved it.  To have a better understanding I have split the expression in a flat table for Static Waterfall. Frontend wise it is same for Static and dynamic waterfall. 

Please leave your comments below.

Attachments
Comments
Not applicable

Hi Rangaraju,

Do you know how I can modify either the code in the load script or the expression used in the chart so that the waterfall can handle negative values in the data?

I'm trying to use the waterfall to show variance (actual vs. plan) so my multiplier needs to be dynamic rather than being hardcoded based on the category as in your example (e.g. positive for revenue, negative for cost).

The top chart in image below is what the chart should look like (created in excel).

The middle is using your formula but with modified colour expression - changing the multiplier didn't seem to make a difference to the bar positions.

For the bottom chart I changed the expression in the chart to include fabs so that it would return absolute values and set the multiplier to 1 for all categories. This seems to work up to 'weather & subs ratio' which has to cross the zero line and from then on the offsetting doesn't work. It also forced my actual positive when it is negative; I couldn't successfully tweak the formula so that fabs wouldn't apply to my two end bars but am assuming that that should be achievable. I'm not sure if it is possible to cross the zero line within QlikSense...?

Any insights would be greatly appreciated!

Thanks,
Peter

if(Dim = 'Value',

fabs(sum(BridgeValue)),

if(RowNo(total)=1 or Offset=0,   0,

RangeSum(Above(total sum(BridgeValue*Multiplier)/2, 1, RowNo(total))) +   RangeMin(0, Sum(BridgeValue*Multiplier))  ) )

Qliksense waterfall query.png

0 Likes
Not applicable

Hi Rangaraju,

The dynamic waterfall model was very helpful. But I need more help with the group by clause. Suppose I have 3 drop down menus say, YEAR, REGION and TYPE. I want to group by all the 3 categories so that my bar chart changes according to the selections made.

I am not able to find a solution for this. I assume I need to add them in the step_2 table as keys.

Really appreciate any help.

Thank you,

Srijhari

0 Likes
Not applicable

Hi Srijhari,

Step_1:

Load

"YEAR",

"REGION",

"TYPE"

SUM(REV_A) as Rev_A,

SUM(REV_B) as Rev_B,

SUM(REV_C) as Rev_C

Resident yourdata Group by "YEAR", "REGION", "TYPE";

Step_2:

Crosstable(stage,amount,3)

load * resident Step_1;

Drop table Step_1;

Not applicable

Hi Peter,

Thank you so much. It was really helpful. I am currently learning Qlik

Sense and I'm eager to know more.

Is there any other alternative email that I can contact you directly for

any queries regarding Qlik Sense? I am a student at Indiana University

Bloomington, pursuing my master's in Data Science.

Really appreciate your help and look forward to hear from you.

Thanks & Regards,

Srijhari

On Wed, Jun 8, 2016 at 1:43 AM, Peter Stewart <qcwebmaster@qlikview.com>

0 Likes
Not applicable

Hi Srijhari,

The Qlik Community is very active and using the discussion forums is the best way to get (the correct) responses to your queries.

I am no expert

Thanks
Peter

0 Likes
Not applicable

Hi,

I have a problem with the waterfall chart. Please find the screenshot attached below for reference. I need the third bar to start after the second bar ends and so on, and not able to figure out how.

Also, how can I get grouped bars in this waterfall chart, suppose I want to compare one dimension against another.

Please help!

Thanks,

Srijhariimg3.png

0 Likes
demonioazul
New Contributor III

Hi Rangaraju,

I have studied the files you posted and leaned some tricks. Before finding this posting I was able to produce Waterfall charts using knowledge from the Community.

One of the charts I have produced with Qlik Sense is this:
2016-08-12 08_16_15-Qlik Sense Desktop - Wtaerfall chart.png

However, since I was a newbie and I could not find a better way, I had to do many Excel massaging to my data to get a table like this:
2016-08-12 08_21_28-Microsoft Excel - Sales Funnel Data.xlsx.png

and that is the table that produces the chart above.

However, there are setbacks for this method:

  1. Still a lot of Excel data manipulation required, since each value comes from a big Sales database. I would like to upload the database to Qlik Sense and dynamically create somehow (Inline Load?) the table to create the Waterfall chart.

  2. If I create the inline table, my guess is that it contains fixed data calculated by using the database. My data comes also from different business units, so the numbers in the table above are totals, but what if want to generate the same chart for one business unit (or a combination of two or more) by using a filter? (Or as it is when you have drill-in variables, for example)

  3. The above chart shows the amount values of the whole column (the sum of the visible part with the offset), not of the visible part of the column only, which is the really interesting part of the column. While extremely useful, I need to copy the chart into a PowerPoint file and edit the amount labels manually.

I would like very, very much that Qlik Sense would have a native way of creating Waterfall charts, not workarounds.
One of my colleagues uses ThinkCell (both for PowerPoint and Excel) and that add-in has native Waterfall charts that are extremely easy to use. I just want to stick to only one BI tool, but doing so it becomes an artisan work dealing with so much data manipulation and then PowerPoint editing...

Thanks,
Demian

Not applicable

Hello,

I would like to change the dynamic waterfall chart in a waterfall chart that is based on months. So that I can choose between the months and the measures change on the chosen month. I have no clue how to do that.

Does anybody have done something like that can help me? Thany you for your answers!

Christian

0 Likes
virat_jb
New Contributor II

Hi Demian,

Did you find any better workarounds to do this? I am also using similar Excel preparations to generate it but not ideal I guess.

Thanks,

Balaji

demonioazul
New Contributor III

Hi Balaji,

No, I haven't found yet a solution within Qlik Sense for these waterfall charts.

I have been in sessions with some Qlik guys where I have received only good hints but not a real way to solve the issue. I have found a way that needs only one step more and it could be done, but QS is missing those steps.

It is reusing the measures I create at load script from two different LOAD statements (two different tables) to generate a new table. If I could do that, then I could generate the table with the offset values and thus the chart would have drill-down capabilities.

I know that Qlik View is able to generate waterfall charts, so my "letter to Santa" is that in version 4.0 Qlik Sense will have these two features:

1. A real easy way to manage colours for measures

and

2. Native waterfall charts

Cheers,

~Demian

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2015-11-08 03:09 PM
Updated by: