topic Re: accumulations help in QlikView

accumulations help

alexpanjhc — Thu, 31 May 2012 19:25:41 GMT

I am trying to accumulate a calculated value in a straight table.

My purpose for this straight table is to create a leadger report.

I have 3 customer, invoice and date and a bunch of calculated amount( fee, expenses, discount ....)

at the end it will have the the amount billed(fee+expenses-dicount...), amount paid and balance due(amount billed- amount paid).

the user wants to see the historical information, so if she looks at 6/1/2012 , she will see the balance due amount for this customer at the time.

I know there is a full accumulation options for the expression, but it did not work. I did some research, some said it only works if there is only one demesion

It should look something like this:

cutomer invoice date fee expense discount billed paid due

ABC 111 01/01/2012 100 50 10 140 140

ABC 111 02/03/2012 50 90

ABC 222 02/23/2012 30 10 20 110

ABC 222 06/01/2012 110 0

instead it looks like this:

cutomer invoice date fee expense discount billed paid due

ABC 111 01/01/2012 100 50 10 140 140

ABC 111 02/03/2012 50 50

ABC 222 02/23/2012 30 10 20 20

ABC 222 06/01/2012 110 110

accumulations help

danielact — Thu, 31 May 2012 19:29:23 GMT

I'm not clear on what exactly it is that you want. Can you please clarify that?

accumulations help

alexpanjhc — Thu, 31 May 2012 20:10:58 GMT

Hi I updated my post. Hope I am more clear now.

thanks!

Re: accumulations help

Sokkorn — Fri, 01 Jun 2012 01:48:38 GMT

Hi,

Not sure I understand you well. Is it your data source look like this?

If this one you looking for then let try

[Data]:

LOAD *,

billed-paid AS Due;

LOAD * INLINE [

cutomer , invoice , date , fee , expense , discount , billed , paid

ABC , 111 , 01-01-2012 , 100 , 50 , 10 , 140 , 0

ABC , 111 , 03-02-2012 , 0 , 0 , 0 , 0 , 50

ABC , 222 , 23-02-2012 , 30 , 0 , 10 , 20 , 0

ABC , 222 , 01-06-2012 , 0 , 0 , 0 , 0 , 110];

Create Straight Table with all field in Dimension and use this one expression: RangeSum(Above(TOTAL Sum(Due),0,RowNo(TOTAL)))

See the sample attached file.

Do let me know.

Regards,

Sokkorn