topic Waterfall charts in QlikView

Waterfall charts

Wed, 27 May 2009 16:06:21 GMT

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

Waterfall charts

Wed, 27 May 2009 16:55:43 GMT

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

Waterfall charts

Oleg_Troyansky — Wed, 27 May 2009 19:45:27 GMT

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

Waterfall charts

Thu, 28 May 2009 07:09:55 GMT

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

Waterfall charts

Mon, 01 Jun 2009 23:07:00 GMT

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.

Waterfall charts

Tue, 22 Dec 2009 00:11:49 GMT

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.