Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
It should be something like:
Load
Sum(If (....
From <> Group By ....;
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
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.
if I were to use aggr function, how do I go about it?
hey thanks! but if I were to use aggr function, how do I go about it?
hey thanks! but if I were to use aggr function, how do I go about it?
You mean, want this in the front -end. Aggr() might not be required. Create a sample qvw and share here explaining the expected output.
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,...
If(rowno()<>NoOfRows(), RangeAvg(Sum(balance), Above(Sum(balance),-1)))
PFA