topic Re: Coping with negative values in waterfall chart in QlikView

Coping with negative values in waterfall chart

Hoowling — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community,

I'm trying to make a waterfall chart showing the dollar change between two periods.
I normally do this via the bar offset function in the expression tab and simpy deduct each added expression from the total, as in below example:

But in this chart the values might be negative OR positive given the period selection (i.e. the values might have increased or decreased between two dates). Using the above expression does not align the bars since both negative and positive values are present.

How do I cope with negative values in the waterfall chart?

Re: Coping with negative values in waterfall chart

settu_periasamy — Thu, 29 Aug 2019 06:59:06 GMT

Can you provide the sample and expected result?

Re: Coping with negative values in waterfall chart

Hoowling — Thu, 29 Aug 2019 17:45:45 GMT

Basically, the data is organized and the chart looks as below:

The expected result is:

Re: Coping with negative values in waterfall chart

marcus_sommer — Fri, 30 Aug 2019 14:32:55 GMT

I think you need to relate the offset against the previous bar(s) and not against the total. For this you could try something like this:

rangesum(above(sum(Value), 0, rowno()))

Depending on your dimensions the above() and rowno() might need an additionally TOTAL statement.

- Marcus

Re: Coping with negative values in waterfall chart

Brett_Bleess — Thu, 05 Sep 2019 20:49:53 GMT

Philip, did Marcus' suggestion work for you? If so, please consider using the Accept as Solution button to mark his post such that he gets credit and others will know that worked for you. I did some digging, but I could not really find anything else that would be of any help on this one.

Regards,
Brett

Re: Coping with negative values in waterfall chart

Hoowling — Fri, 06 Sep 2019 14:40:39 GMT

The solution for my problem was to deduct the each column repectively using the column()-function.

E.g. for the 5th bar in the chart, I used Column(1)+Column(2)+Column(3)+Column(4). Worked perfectly.