Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I Need help..
These are my fields, i have to compare data PostDate wise.
Key | PostDate | PostID01 | PostID02 | Amount |
I want to calculate 2 fields.
Case1:
If Year=2014, compare with PostID01
PostDate: 31/01/2014 (Month End)
PostID01 = ACCOUNT
Sum(Amount) as FilterData_IN
PostDate: 01/02/2014 (Month Start)
PostID01 = ACCOUNT1
Sum(Amount) as FilterData_OUT
Case2:
If Year=2015, compare with PostID02
PostDate: 31/01/2015 (Month End)
PostID02 = SUB06
Sum(Amount) as FilterData_IN
PostDate: 01/02/2015 (Month Start)
PostID02 = 2015
Sum(Amount) as FilterData_OUT
Output Should be
Calculated Field | 2014 | 2015 |
Post_IN | $ 1,984,40 | $ 2,539,412.90 |
Post_Out | $ 2,365,559.11 | $ 1,718,012.59 |
Thanks in Advance..
PFA,
Maybe add something along these lines to your script:
RESULT:
LOAD YEAR, FILTER,
Sum(Amount) as SUM
WHERE Len(Trim(FILTER))
GROUP BY YEAR, FILTER;
LOAD *,
Year(PostDate) as YEAR,
If( ((Year(PostDate)=2014 and PostID01 = 'ACCOUNT') or (Year(PostDate)=2015 and PostID02 = 'SUB06')) and PostDate = Floor(MonthEnd(PostDate)), 'FilterData_IN'
, If(((Year(PostDate)=2014 and PostID01 = 'ACCOUNT1') or (Year(PostDate)=2015 and PostID02 = '2015')) and PostDate = Floor(Monthstart(PostDate)), 'FilterData_OUT')) as FILTER
Resident INPUT; //I've labelled your INLINE table INPUT
FILTER | YEAR | 2014 | 2015 |
---|---|---|---|
FilterData_IN | 198440,00 | 2539412,89 | |
FilterData_OUT | 2365559,10 | 1718012,60 |