accumulations help

[alexpanjhc]

Hi

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

[danielact]

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

[alexpanjhc]

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

thanks!

[Sokkorn · Accepted solution]

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