topic Re: Need help regarding Water Flow Chart in QlikView

Need help regarding Water Flow Chart

Wed, 05 Aug 2015 17:51:18 GMT

In my excel file there are 3 columns

BU,Prev Year and Current Year,

In my waterfall chart,

there are 9 bars

1st one is PrevYear

2nd to 8th is BU1 to BU7

it is varience of current year to last year

9th bar is current year

Prev year and current year is in blue color and r at 0 level,and in BU1 (Value is Current year-prev year)

and it is green if>0

and red if< 0

like that

Im sending the input excel file and output image..

Pls tell me how to do dat in water fall chart

Re: Need help regarding Water Flow Chart

settu_periasamy — Thu, 06 Aug 2015 03:13:54 GMT

Hi Himesh,

Exactly the same requirement is available in the below link.

//labs.ecraft.com/2011/03/16/how-to-create-waterfall-charts-in-qlikview/

(http removed infront of //)

find attached the qvw from the above link.

Re: Need help regarding Water Flow Chart

Thu, 06 Aug 2015 03:22:03 GMT

Hi settu,

i didnt undrstand from the above link.

in that waterfall column prevyear,Bu1 to Bu7,current year,

if we taken as dimension its not getting correctly,

how do u interlink both the tables,

pls explain.

Re: Need help regarding Water Flow Chart

settu_periasamy — Thu, 06 Aug 2015 04:30:14 GMT

Hi Himesh,

i didn't get this.

if we taken as dimension its not getting correctly,

we need to create separate table for ActMultiplier and PrevMultiplier. the key field is BU.

Re: Need help regarding Water Flow Chart

Thu, 06 Aug 2015 08:21:07 GMT

In my edit script I have

LOAD * INLINE [
BU, PrevYear, CurrentYear, ActMultiplier, CurrMuktiplier
BU1, 62, 68
BU2, 83, 85
BU3, 82, 79
BU4, 73, 78
BU5, 22, 23
BU6, 29, 28
BU7, 18, 27
Total, 369, 388
];

I have like dis

wat do fill in ActMultiplier And CurrMultiplier

In bar chart

what is the dimension?

Expression?

barr offset:?

background Color?

Pls respond

Re: Need help regarding Water Flow Chart

settu_periasamy — Thu, 06 Aug 2015 10:02:50 GMT

Hi Himesh,

from the help of the link //labs.ecraft.com/2011/03/16/how-to-create-waterfall-charts-in-qlikview/

The dimension is based on BU.

the script

Data:

LOAD * INLINE [

BU, CurrentYear, PrevYear

ALL, 388, 369

BU1, 68, 62

BU2, 85, 83

BU3, 79, 82

BU4, 78, 73

BU5, 23, 22

BU6, 28, 29

BU7, 27, 18

];

WaterfallDefinitions:

LOAD * INLINE [

BU, Waterfall1, OrderBy, WaterfallFormatting, OffsetRowsBack, ActMultiplier, PrevMultiplier

ALL, Prev Year, 0, Base, 0, 0, 1

BU1, BU 1, 1, RedGreen, 1, 1, -1

BU2, BU 2, 2, RedGreen, 2, 1, -1

BU3, BU 3, 3, RedGreen, 3, 1, -1

BU4, BU 4, 4, RedGreen, 4, 1, -1

BU5, BU 5, 5, RedGreen, 5, 1, -1

BU6, BU 6, 6, RedGreen, 6, 1, -1

BU7, BU 7, 7, RedGreen, 7, 1, -1

ALL, Curr Year, 8, Base, 0, 1, 0

];

Dimension: Waterfall1

Expression Tab

Expression: Sum(CurrentYear)*ActMultiplier+Sum(PrevYear)*PrevMultiplier

Background Colour:

if(only(WaterfallFormatting)='Base',

Blue(),

if(column(1) <0,Red(),Green())

)

BarOffset : rangesum (above(Column(1),1,only(OffsetRowsBack)))

Sort Tab

Business Unit -> Expression (Ascending)-> OrderBy