Waterfall charts

[#1]

Hi,

I need to put together a Waterfall chart like this to display Cash flow.

I realise there is a Bar-Offset property behind the expressions, but I'm not too sure how I could be able to capture the offset.

The chart would have a starting cash balance and then receivables, receipts and a few more flows to arrive at a closing balance. These could be condenced into a transaction type dimension.

The problem I am having is understanding how to then reference the sum from the preceeding dimensions. I guess I could set the dimensions up to start with a number, and then sum up the dimensions with a number less that the one I'm painting, but this seems a bit clunky.

Has anyone put a similar type of chart together that they can send me an example of ?

Many thanks,

Jonathan

[#2]

Hi,

Can you post the qlik document so that we will look in to the document. here i have attached the sample. Check P&L sheet. Let me know if this help you to solve your problem.

Thanks,

Raghu

[Oleg_Troyansky · Accepted solution]

It's easier to implement if your bars represent different Expressions (like in th P&L example), If you'd like to do it with Dimenion Values, you could try one of the two:

1. Use function above() to get the previous values.

2. Attach some numeric keys to your transaction codes and use Set Analysis to summarize all transactions with the numeric code up to a certain number. For example:

Closing Balance - 0

Receivables - 10

Payables - 20

...

The offset value for each TransCode would be:

sum({$<TransCode={"<only(TransCode)"}>} TransactionAmount)

or something like this...

Oleg

[#4]

Hi,

Raghu, I'm just thinking ahead at the moment so I haven't got either the data or the qvw document yet.

Oleg, thanks for your suggestions. I think the above() function will be very useful here.

Jonathan

[#5]

Hi

It is also possible to reference columns by name, so for example if you have expressions as follows:

Opening Balance

Amount Paid

Amount Received

Closing Balance

You could create a bar offset expression that is something like:

For amount paid: =[Opening Balance]

For Amount Received: =[Opening Balance] - [Amount Paid]

etc, etc

Good Luck,

Nigel.

[#6]

Hello all,

Case example of a waterfall chart that will work universally and update automatically for new data and new dimension values being incorporated:

1. Setup a bar chart with Dimension = X and expression = expY.

2. In the offset, set up an expression such as rangesum(above(expY), 1, rowno()). This should work universally.

Limitation to this kind of waterfall chart, you cannot generate a unique Total bar chart at the end of the bar chart.