topic Re: calculating sum for previous months including current one in QlikView

calculating sum for previous months including current one

jacek_sobczak — Thu, 21 Jul 2016 21:39:30 GMT

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

Re: calculating sum for previous months including current one

settu_periasamy — Fri, 22 Jul 2016 05:40:11 GMT

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

Re: calculating sum for previous months including current one

karthikoffi27se — Fri, 22 Jul 2016 05:44:38 GMT

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

Re: calculating sum for previous months including current one

tresB — Fri, 22 Jul 2016 05:52:46 GMT

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.

Re: calculating sum for previous months including current one

tresB — Fri, 22 Jul 2016 05:58:34 GMT

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;

Re: calculating sum for previous months including current one

Fri, 22 Jul 2016 06:02:33 GMT

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