Waterfall chart with multi measures

[Former Member]

Hi,

We are in BI 4.1 SP5.

I need to build waterfall chart.

Currently i have data in following format

Month	Measure1	Measure2	Measure3	Measure 4	Measure 5
Jan	50	52	-20	30	90
Feb	24	45	-10	20	80
Mar	60	34	-15	45	77

I need to have waterfall chart for each month

Since waterfall chart is allowing 1 measure, I am not able to build it.

Please advise how to build the waterfall chart in webi

Regards

Ben

[Former Member · Accepted solution]

Hello Ben,

As far as I know Waterfall chart will accept only one measure. Can you make use of Stacked bar chart instead?

Regards

Niraj

[Former Member · Accepted solution]

Hi Niraj, Customer needs this in Waterfall chart. They were able to do this in excel

[mhmohammed · Accepted solution]

Hi Ben,

The Measure 1 value seems to be around 2M in this Chart, so, is that number the total for all the Months of just one Month (assuming that you've data for multiple months, from your initial post)?

Thanks,
Mahboob Mohammed

[mhmohammed · Accepted solution]

Hi Ben,

Important Note just updated: Please scroll down, you'll find a scroll bar as the message is being cut off (due to width), you'll have to scroll to right to see the whole thing (in some lines and the tables I have created).

With some remodeling of the data you attached in your initial post, I was able to create it as below.

This is how the data looked after remodeling (trust me, its the same data that you have in your initial post, its just pivoted). We'll work on your requirement using the similar logic.

MonthNumber	MonthName	MeasureName	Value
1	Jan	Measure 1	50
1	Jan	Measure 2	52
1	Jan	Measure 3	-20
1	Jan	Measure 4	30
1	Jan	Measure 5	90
2	Feb	Measure 1	24
2	Feb	Measure 2	45
2	Feb	Measure 3	-10
2	Feb	Measure 4	20
2	Feb	Measure 5	80
3	Mar	Measure 1	60
3	Mar	Measure 2	34
3	Mar	Measure 3	-15
3	Mar	Measure 4	45
3	Mar	Measure 5	77

Long story short, the trick is to create combined queries. Below are the steps, how to achieve it:

1. Create 5 (or more, as many measures as you have) dummyDimension objects in the Universe with the Object Name, the text in their Select clause, Description as in the below table and all of them should be String datatype. These dummy objects will serve as labels in the resultant data. Note: I'm using real names so it'll be easy for you to replace it with the actual measure names you have.

   Object Name	Text in Select Clause (single quotes required obviusly)	Description for this Object to help everyone
   Measure	'Sales Revenue'	Measure, to be used in Combined Query 1, in which you'll include the Sales Revenue measure. The name of the resultant object after combining after all the combined queries will be the name of this object Measure and we want to see a generic name for this object, instead of  Sales Revenue / Profit / Cost...
   Measure 2	'Profit'	Measure 2, to be used in Combined Query 2
   Measure 3	'Cost'	Measure 3, to be used in Combined Query 3
   Measure 4	'Margin'	Measure 4, to be used in Combined Query 4
   Measure 5	'Budget'	Measure 5, to be used in Combined Query 5

2. In the report, you'll have to create 5 combined queries. Each combined query will have all the dimensions you need, only one Measure (not all 5 measures), and One of those dummy objects you created in the Universe. Make sure, you use Measure dummy object in combined query 1 and Measure 2 in combined query 2 and so on. Below is the list of Combined queries and all the objects that it should be pulling (filters in all the combined queries should be the same, you'll have to create manually)
   • Combined Query 1: Dimension 1, Dimension 2, Dimension 3,...Measure, Sales Revenue (actual measure)
   • Combined Query 2: Dimension 1, Dimension 2, Dimension 3,...Measure 2, Profit (actual measure)
   • Combined Query 3: Dimension 1, Dimension 2, Dimension 3,...Measure 3, Cost (actual measure)
   • Combined Query 4: Dimension 1, Dimension 2, Dimension 3,...Measure 4, Margin (actual measure)
   • Combined Query 5: Dimension 1, Dimension 2, Dimension 3,...Measure 5, Budget (actual measure)
   • Note: I'm assuming that you know and will take care of pre-reqs to make combined queries work, such as,
     • The number of objects in all combined queries should be same
     • The datatype of object at each position should be the same
     • (In your case) the filters in all the combined queries should be the same
3. Run the query. As you know, the query is pulling data in a little different format. Try creating a table with all the Objects that you've pulled in the report in a table. Then, filter on the Measure object = Sales Revenue and you'll see Sales Revenue data only in the column, if you filter for Cost, you'll see Cost data in the same column.
4. Create a Section for MonthName in the report
5. In the Section, create a Waterfall Chart with Measure object in Category axis and Sales Revenue (actual measure) in the Value Axis 1 and Voila, you'll see what you needed.

How cool is that? Let us know if you have any questions.

Thanks,
Mahboob Mohammed

[Former Member · Accepted solution]

Thanks Mahbood for wonderful detailed reply, much appreciated. I will try out the same and feedback to you

[mhmohammed · Accepted solution]

Hi Ben,

Just so you know, if you see the image I've in my post, there is an overlap between Measure 2, Measure 3 and Measure 4 which is not how a Waterfall Chart should work. The reason for that overlap is because the sample data you provided has Negative (-ve) values for Measure 3 and hence the bar goes down instead of going up.

Make sense?

Thanks,

Mahboob Mohammed

[Former Member]

Hi,

Have you referred below docs?

BOBJ Tricks: Waterfall Charts in Webi

Charting in Web Intelligence Part 4 – Tag Clouds & Water Fall Charts | Altek Solu...

[Former Member]

Hi Please look into the query, the attached link will help to build waterfall chart only by the 1 single measure. i need to build with multiple measures Ben

[Former Member]

Hi Ben,

I misunderstood your problem.. Can you show a sample output of your chart.

You want to show Month wise change in all these measures?

[mhmohammed]

Hi Ben,

I don't see an excel attached, please attach a notepad file with the data or just attach the whole table with data as you did in your initial post, we'll be able to copy it.

I've an idea, will try that and let you know if that works.

Thanks,

Mahboob Mohammed