topic Re: Accumulate Month wise in table in QlikView

Accumulate Month wise in table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Accumulate Month wise in table

YoussefBelloum — Mon, 05 Mar 2018 23:31:52 GMT

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

Re: Accumulate Month wise in table

Anonymous — Mon, 05 Mar 2018 23:35:26 GMT

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.

Re: Accumulate Month wise in table

YoussefBelloum — Mon, 05 Mar 2018 23:39:21 GMT

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 ?

Re: Accumulate Month wise in table

Anonymous — Mon, 05 Mar 2018 23:40:54 GMT

Yes

Re: Accumulate Month wise in table

YoussefBelloum — Mon, 05 Mar 2018 23:44:48 GMT

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 ?

Re: Accumulate Month wise in table

Anonymous — Mon, 05 Mar 2018 23:49:04 GMT

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

Re: Accumulate Month wise in table

YoussefBelloum — Mon, 05 Mar 2018 23:55:23 GMT

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

Re: Accumulate Month wise in table

Anonymous — Tue, 06 Mar 2018 00:07:32 GMT

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

That is the Date column you are talking about?

Re: Accumulate Month wise in table

YoussefBelloum — Tue, 06 Mar 2018 00:19:04 GMT

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

Re: Accumulate Month wise in table

Anonymous — Tue, 06 Mar 2018 00:22:36 GMT

I have it like that. Please find attached sample.

Hope its clear

Re: Accumulate Month wise in table

YoussefBelloum — Tue, 06 Mar 2018 00:38:22 GMT

here is the expression to calculate your opening capital cumulation (or other Measure cumulation on dates):

choose the transaction date a Dimension

=RangeSum(Above(sum(OPENING_CAPITAL),0,RowNo()))

and here is the expression for the YTD (it will work on your 2018 data)

=sum({<TRANSACTION_DATE={'>=$(=YearStart(Today()))<=$(=Today())'}>}OPENING_CAPITAL)

for previous year (2017 for example):

=sum({<TRANSACTION_DATE={$(=YearStart(Today(),-1)}>}OPENING_CAPITAL)

Re: Accumulate Month wise in table

Anonymous — Tue, 06 Mar 2018 00:43:37 GMT

Can you please attach qvw, I want to check for final accumulation as the value has to match with excel values.

As I am not getting correct values from the above expressions.