add values of a column for the start of each month

[#1]

Hi, how should the script be like if I want to add up the balance(refer below) for 1 Jan 14 and 1 Feb 14, 1 Feb 14 and 1 Mar 14, etc,...?

itemnum	balance	month
2	3	1 Jan-14
2	10	1 Feb-14
2	6	1 Mar-14
2	4	1 Apr-14
2	8	1 May-14

Thanks.

[tresesco]

It should be something like:

Load

          Sum(If (....

From <> Group By ....;

[jonathandienst]

Hi

Something like

T_Data:

LOAD intemnum,

  balance,

  Date(MonthStart(date#(month, 'd mmm-yyyy'))) As month

Inline

[

  intemnum, balance, month

  2, 3, 1 Jan-14

  2, 10, 1 Feb-14

  2, 6, 1 Mar-14

  2, 4, 1 Apr-14

  2, 8, 1 May-14

];

Data:

LOAD *,

  If(itemnum = Previous(itemnum), Alt(Previous(balance), 0), 0) + balance As cum2months

Resident T_Data;

DROP Table T_Data;

Regards

Jonathan

[Anonymous]

Hi,

1:

load * inline
[
itemnum,balance,month
2,3,1-Jan-14
2,10,1-Feb-14
2,6,1-Mar-14
2,4,1-Apr-14
2,8,1-May-14
];
2:
load *,date(Date#(month,'DD-MMM-YY'),'M/D/YY') AS DATE
Resident 1;
DROP Table 1;

DROP Field month;

LOAD if(Previous(DATE)=AddMonths(DATE,-1),balance+Previous(balance),0) as new
Resident 2;

HTH.

Ravi N.

[#5]

if I were to use aggr function, how do I go about it?

[#6]

hey thanks! but if I were to use aggr function, how do I go about it?

[#7]

hey thanks! but if I were to use aggr function, how do I go about it?

[tresesco]

You mean, want this in the front -end. Aggr() might not be required. Create a sample qvw and share here explaining the expected output.

[#9]

Hi, this is the expected output:

itemnum	balance	month	avg balance
2	3	1 Jan-14	6.5
2	10	1 Feb-14	8
2	6	1 Mar-14	5
2	4	1 Apr-14	6
2	8	1 May-14	-

where 'avg balance'=sum(balance)/2 for 1 Jan-14 and 1 Feb-14, 1 Feb-14 and 1 Mar-14, etc,...

[tresesco]

If(rowno()<>NoOfRows(), RangeAvg(Sum(balance), Above(Sum(balance),-1)))

PFA