calculating sum for previous months including current one

[jacek_sobczak]

Hi,

I'm in trouble to get proper result.

I have a table like below:

Sample:

LOAD * INLINE [

    YearMonth, Sales

    '2009-01', 55

    '2009-02', 39

    '2009-03', 33

    '2009-04', 56

    '2009-05', 55

    '2009-06', 70

    '2009-07', 57

    '2009-08', 29

    '2009-09', 34

    '2009-10', 41

    '2009-11', 12

    '2009-12', 11

    '2010-01', 24

    '2010-02', 85

    '2010-03', 99

    '2010-04', 77

    '2010-05', 85

    '2010-06', 64

    '2010-07', 61

    '2010-08', 45

    '2010-09', 62

    '2010-10', 70

    '2010-01', 59

    '2010-12', 25

];

and I want to calculate for each month sum of all previous months and current one. Result should be like this:

I will very appreciated for any help

Best regads, Jacek

[settu_periasamy]

not able to see the screen shot. can you attach the screen shot again?

[karthikoffi27se]

Hi Jacek,

You can use this expression to calculate some of each month and previous month.

Let vMonth = Date(Monthend(Today()),'YYYY-MM')

Let vPreviousmonth = Date(Monthstart(Addmonths(Today(),-1)),'YYYY-MM')

Sum({<YearMonth = {"<=$(vMonth) >=$(vPreviousmonth)"} >} Sales)

This should work..

Many Thanks

Karthik

[tresesco]

Try:

Sample:

Load

  *,

  If (Year=Previous(Year), RangeSum(Sales,Peek(AccSum)), Sales) as YearlyAccSum;

LOAD *,

  Year(Date#(YearMonth,'YYYY-MM')) as Year,

  month(Date#(YearMonth,'YYYY-MM')) as Month INLINE [

    YearMonth, Sales

    '2009-01', 55

    '2009-02', 39

    '2009-03', 33

    '2009-04', 56

    '2009-05', 55

    '2009-06', 70

    '2009-07', 57

    '2009-08', 29

    '2009-09', 34

    '2009-10', 41

    '2009-11', 12

    '2009-12', 11

    '2010-01', 24

    '2010-02', 85

    '2010-03', 99

    '2010-04', 77

    '2010-05', 85

    '2010-06', 64

    '2010-07', 61

    '2010-08', 45

    '2010-09', 62

    '2010-10', 70

    '2010-01', 59

    '2010-12', 25

];

Note: Assumption - input data is sorted. Data is accumulated yearly.

[tresesco]

If you need total accumulation i.e. across years, you have to just remove the year match condition, like:

Sample:

Load

  *,

  RangeSum(Sales,Peek(AccSum)) as AccSum;

[#6]

Couldnt view the second screenshot. If this is what you meant, you may simply try accumulation under expression tab.