Running sum of only Previous and Current Month

[Anonymous]

Hi,

Following Data set :

Dimension	Month	Amount
B1	Jun	5
	July	6
	August	15
	September	12
	October	18
	November	2
	December	6
B2	Jun	24
	July	0
	August	21
	September	9
	October	56
	November	41
	December	20

what i want is to add previous Amount values in the current month and show zero values in the previous month.

Like for Example For Dimension B1 :

Current Month August 

it should show 15+6+5 = 26 instead of 15 , and show zero values in Jun and July.

What i was doing in the script

IF(Month(Date#(Month_field,'YYYYMMDD'))=Month(Today()),Previous(Rangesum((Amount))),Amount) AS Amount,

the issue is, its range sum the very previous record in the field but not all the values.

like for B1 Dimension for August i got the value 21 instead of 26

and in B2  Dimension for August i got the value 21 as the very previous record is 0.

is there any way we can add the all the previous amount values into one.

Thanks

[Anonymous]

I will be needing your input on this gwassenaar‌

[sunny_talwar]

Would you be able to provide the complete script?

[Anonymous]

Table:

Load

Month_field,

IF(Month(Date#(Month_field,'YYYYMMDD'))=Month(Today()),Previous(Rangesum((Amount))),Amount) AS Amount,

Amount,

Dimension

From sales.qvd(qvd);

[rajuamet]

Hi Osama Anwar,

Considering this month as September, I have constructed the table. If this is the required Output, PFA.

[Anonymous]

Considering this month as September, the Required amount field is above, PFA

[sunny_talwar]

May be this?

=If(MonthName(Month) = MonthName(Today()), Rangesum(Above(Amount,0,RowNo())),

If(MonthName(Month) > MonthName(Today()), Sum(Amount), 0))

Modified raju_insights‌'s attached application to get this

[Anonymous]

I want to have this on Script level not on Chart Expression as Above function wont work on Script.

[sunny_talwar]

Try this script:

Table:

LOAD * INLINE [

    Dimension, Month, Amount

    B1, 6/1/16, 5

    B1, 7/1/16, 6

    B1, 8/1/16, 15

    B1, 9/1/16, 12

    B1, 10/1/16, 18

    B1, 11/1/16, 2

    B1, 12/1/16, 6

    B2, 6/1/16, 24

    B2, 7/1/16, 0

    B2, 8/1/16, 21

    B2, 9/1/16, 9

    B2, 10/1/16, 56

    B2, 11/1/16, 41

    B2, 12/1/16, 20

];

FinalTable:

LOAD *,

  If(Dimension = Previous(Dimension), RangeSum(Peek('AccAmt'), Amount), Amount) as AccAmt,

  If(MonthName(Month) = MonthName(Today()),

  If(Dimension = Previous(Dimension), RangeSum(Peek('AccAmt'), Amount), Amount),

  If(MonthName(Month) > MonthName(Today()), Amount, 0)) as NewAmount

Resident Table

Order By Dimension, Month;

DROP Table Table;

DROP Field AccAmt;

[Anonymous]

this is what i am getting when i plug in my data with the above script,

i want to rangesum only Current Quarter Month Values like , in the amount field for the month of '09' the value is 1387 , what i want is to club 2 in it which fall in Month field '08' so the required value is 1389 no 1387.