Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
alfredlee
New Contributor III

Set Analysis on Previous Month

I have a simple table

YearMonthMonthEnd Balance
20121100
20122200
20123300
20124400
20125500
20126600

and I would need to display the data in the following format:

YearMonthOpening BalanceMonthEnd Balance
201210100
20122100200
20123200300
20124300400
20125400500
20126500600

I think this is to do with the Set Analysis but I do not know how to get the Opening Balance Expression correct.

Please help and thanks in advance.

1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis on Previous Month

Hi

Use this for opening balance in a chart like that in your post

= Above[MonthEnd Balance]

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
2 Replies
MVP
MVP

Re: Set Analysis on Previous Month

Hi

Use this for opening balance in a chart like that in your post

= Above[MonthEnd Balance]

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Sokkorn
Honored Contributor

Re: Set Analysis on Previous Month

Hi Alfred,

You can try Above([MonthEnd Balance]) in your expression.

Procedure

1. Load Data

[Data]:

LOAD * INLINE [

Years,    Months,    MonthEnd Balance

2012,    1,    100

2012,    2,    200

2012,    3,    300

2012,    4,    400

2012,    5,    500

2012,    6,    600];

2. Create Straight Table with:

     a. Years and Months as Dimension

     b. Two Expression

          i. SUM([MonthEnd Balance])

          ii. Above([MonthEnd Balance])

See the sample attached file also.

Regards,

Sokkorn