Opening Date Wise On every Row..

[vijayit43]

Hi Expert,

              In QlikView Pivot chart we want to cumulative Opening date wise in a row from a given period,  for detail example given bellow

Code               Date               Amount

cust/001     01/02/2015               5

cust/001     02/02/2015               10

cust/001     03/02/2015               15 

cust/001     04/02/2015               20

cust/001     05/02/2015               25

cust/001     06/02/2015               30

cust/001     07/02/2015               35

cust/001     08/02/2015               40

cust/001     09/02/2015               45

cust/001     10/02/2015               50

when we give filter date from 04-02-2015 to 09-02-2015 the show result at this type

Code           Date               Opening

cust/001     04/02/2015          30

cust/001     05/02/2015          50

cust/001     06/02/2015          75

cust/001     07/02/2015          105

cust/001     08/02/2015          140

cust/001     09/02/2015          185

how to achieve this please suggest

[trdandamudi]

One of the way you can resolve this is as below:

Source:

Load Code,

Date,

Amount,

numsum(Amount, Peek(Opening))as Opening;

Load * Inline [

Code,               Date,               Amount

cust/001,     01/02/2015,               5

cust/001,     02/02/2015,               10

cust/001,     03/02/2015,               15

cust/001,     04/02/2015,               20

cust/001,     05/02/2015,               25

cust/001,     06/02/2015,               30

cust/001,     07/02/2015,               35

cust/001,     08/02/2015,               40

cust/001,     09/02/2015,               45

cust/001,     10/02/2015,               50

];

Now Create a straight Table as below:

Dimensions:

Code

Date

Expressions:

=(Opening-Amount)

Label: Opening

Below is the final screen shot:

Hope this helps...

[senpradip007]

PFA. Hope it will help.

[trdandamudi]

I did submitted a solution this morning but it is under "Currently Being Moderated" because I pasted few screen shots. But here is one more solution...

Source:

Load Code,

Date,

Amount,

numsum(Amount, Peek(Opening)) as Opening;

Load * Inline [

Code,               Date,               Amount

cust/001,     01/02/2015,               5

cust/001,     02/02/2015,               10

cust/001,     03/02/2015,               15

cust/001,     04/02/2015,               20

cust/001,     05/02/2015,               25

cust/001,     06/02/2015,               30

cust/001,     07/02/2015,               35

cust/001,     08/02/2015,               40

cust/001,     09/02/2015,               45

cust/001,     10/02/2015,               50

];

Final:

Load Code,

Date,Amount,

Opening - Amount as Opening_Balance

resident Source;

drop table Source;

In this method you will have everything in one table.