Accumulate Month wise in table

[Anonymous]

Hi all,

I am trying to achieve cumulative sum in table chart month wise. I have attached a sample.

Till now I could get till this,

=RangeSum(Above(If(Only(Transaction_Date)=min(total  Transaction_Date), Sum(P_AND_L)/Sum(OPENING_CAPITAL),

((( Sum(P_AND_L)/Sum(OPENING_CAPITAL))+1)*rangesum(above(P_AND_L)/(OPENING_CAPITAL)))-1)), 0, NoOfRows())

Any help on this? Even i need to find best and worst value in accumulated sums.

[YoussefBelloum]

Hi,

here I see two transaction dates..

also can you explain the calculation rule ?

starting from your expression just translate your conditions, how do you want to calculate your cumulative values

[Anonymous]

I want to calculate the MTD value. Transaction Dates are just arranged in Month wise .

First I want to calculate TDY which is Pnl/opening capital and then accumulate it.

[YoussefBelloum]

It is ok, I'm asking the question because you'ill be able to calculate your MTD using these fields.

did you copy and paste these columns manually on this file ?

[Anonymous]

Yes

[YoussefBelloum]

That's what I thought, you paste the columns the wrong way, you should just concatenate them actually instead of doing this, otherwise you will have to do it in the script.

so what is your original file structure ?

[Anonymous]

I am attaching my file structure and i want to calculate column G in it.

[YoussefBelloum]

with this structure, you can't do nothing,

• there is no dimension column for the X-axis (so no cumulative/Range values).

• there is no Date column

[Anonymous]

I want to achieve accumulation using Transaction_Date as dimension in table chart.

That is the Date column you are talking about?

[YoussefBelloum]

Yes, that's it.

the problem is that you have a wrong structure, I'ill explain with an example:

you file now (i removed some columns)

OPENING_CAPITAL	TRANSACTION_DATE	OPENING_CAPITAL	TRANSACTION_DATE
11296695	03-mai-16	10309207	01-juin-16
10756219	04-mai-16	10344781	02-juin-16
10818340	05-mai-16	10430606	03-juin-16
10716857	06-mai-16	10578898	07-juin-16

you should have it like this:

OPENING_CAPITAL	TRANSACTION_DATE
11296695	03-mai-16
10756219	04-mai-16
10818340	05-mai-16
10716857	06-mai-16
10309207	01-juin-16
10344781	02-juin-16
10430606	03-juin-16
10578898	07-juin-16

hope it is clear