Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
How can I get the sum of amount where the items month is less than the maximum month?
My Data as below:
Name | Segment | Pay Date | Pay Mth | Amount |
---|---|---|---|---|
Acc 1 | New | 12/5/2016 | May-2016 | 1000 |
Acc 2 | Used | 21/5/2016 | May-2016 | 50000 |
Acc 3 | Others | 3/6/2016 | Jun-2016 | 17263.22 |
Acc 4 | New | 15/6/2016 | Jun-2016 | 16036.2 |
Acc 5 | New | 25/6/2016 | Jun-2016 | 2500 |
Acc 6 | New | 29/6/2016 | Jun-2016 | 14000 |
Acc 7 | Others | 30/6/2016 | Jun-2016 | 13322.58 |
Acc 8 | Used | 8/7/2016 | Jul-2016 | 1523 |
Acc 9 | Used | 8/7/2016 | Jul-2016 | 1125 |
Acc 10 | Others | 10/7/2016 | Jul-2016 | 18326 |
Acc 11 | New | 13/7/2016 | Jul-2016 | 562.3 |
Acc 12 | Others | 15/7/2015 | Jul-2016 | 17.13 |
DATA:
NoConcatenate
LOAD * Inline [
Name,Segment,Pay Date,Pay Mth,Amount
Acc 1,New,42502,42521,1000
Acc 2,Used,42511,42521,50000
Acc 3,Others,42524,42551,17263.22
Acc 4,New,42536,42551,16036.2
Acc 5,New,42546,42551,2500
Acc 6,New,42550,42551,14000
Acc 7,Others,42551,42551,13322.58
Acc 8,Used,42559,42582,1523
Acc 9,Used,42559,42582,1125
Acc 10,Others,42561,42582,18326
Acc 11,New,42564,42582,562.3
Acc 12,Others,42200,42582,17.13
];
Currently I like to do comparation the total amount for each Pay Mth. For the maximum month, I already do the espression as below:
=sum({$<[Pay Mth]={'$(=max([Pay Mth]))'}>} Amount)
Result
My Question:
How to get May-16 and Jun-16 into the table, where by:
a June-16 is less than 1 from Jul-2016
b. May-16 is less than 2 from Jul-2016
My bad, I did not see the formatting applied to Pay Mth in the script
Try this:
DATA:
NoConcatenate
LOAD * Inline [
Name,Segment,PayDate,Amount
Acc 1,New,42502,1000
Acc 2,Used,42511,50000
Acc 3,Others,42524,17263.22
Acc 4,New,42536,16036.2
Acc 5,New,42546,2500
Acc 6,New,42550,14000
Acc 7,Others,42551,13322.58
Acc 8,Used,42559,1523
Acc 9,Used,42559,1125
Acc 10,Others,42561,18326
Acc 11,New,42564,562.3
Acc 12,Others,42200,17.13
];
Test:
NoConcatenate
LOAD Name,
Segment,
Date(PayDate,'DD/MM/YYYY') as [Pay Date],
Amount
Resident DATA;
Left Join (Test)
LOAD Name,
Date(Floor(MonthEnd([Pay Date])),'MMM-YYYY') as [Pay Mth]
Resident Test;
DROP Tables DATA;
Three Expressions:
May: =Sum({$<[Pay Mth]={"$(=Date(AddMonths(Max([Pay Mth]), -2), 'MMM-YYYY'))"}>} Amount)
June: =Sum({$<[Pay Mth]={"$(=Date(AddMonths(Max([Pay Mth]), -1), 'MMM-YYYY'))"}>} Amount)
July: =Sum({$<[Pay Mth]={'$(=max([Pay Mth]))'}>} Amount)
Have you tried this?
=Sum({$<[Pay Mth]={'$(=Max(AddMonths([Pay Mth], -1)))'}>} Amount) -> June
=Sum({$<[Pay Mth]={'$(=Max(AddMonths([Pay Mth], -2)))'}>} Amount) -> May
Actually, that might not work, try this instead:
=Sum({$<[Pay Mth]={$(=Num(Max(AddMonths([Pay Mth], -1))))}>} Amount)
=Sum({$<[Pay Mth]={$(=Num(Max(AddMonths([Pay Mth], -2))))}>} Amount)
Thanks stalwar1for your reply.... Its work for my sample above.
However, I got problem when do the script in my actual data.
Actually the 'Pay Mth' field is derive from 'Pay Date' field. I already used the script given by you but got result as 0 value.
Pls assist:
SQL Data:
DATA:
NoConcatenate
LOAD * Inline [
Name,Segment,PayDate,Amount
Acc 1,New,42502,1000
Acc 2,Used,42511,50000
Acc 3,Others,42524,17263.22
Acc 4,New,42536,16036.2
Acc 5,New,42546,2500
Acc 6,New,42550,14000
Acc 7,Others,42551,13322.58
Acc 8,Used,42559,1523
Acc 9,Used,42559,1125
Acc 10,Others,42561,18326
Acc 11,New,42564,562.3
Acc 12,Others,42200,17.13
];
Test:
NoConcatenate
LOAD Name,
Segment,
Date(PayDate,'DD/MM/YYYY') as [Pay Date],
Amount
Resident DATA;
Left Join (Test)
LOAD Name,
date(MonthEnd([Pay Date]),'MMM-YYYY') as [Pay Mth]
Resident Test;
DROP Tables DATA;
For Expression:
May
Sum({$<[Pay Mth]={$(=Num(Max(AddMonths([Pay Mth], -2))))}>} Amount)
Jun
Sum({$<[Pay Mth]={$(=Num(Max(AddMonths([Pay Mth], -1))))}>} Amount)
Jul
sum({$<[Pay Mth]={'$(=max([Pay Mth]))'}>} Amount)
Note: Unable to get the result for Jun and May.
Since you Pay Mth field is formatted as date, you will need to format using Date() instead of Num() in your set analysis. You might also need to add single quotes around the dollar sign expansion (changes and additions in red):
For Expression:
May
Sum({$<[Pay Mth]={'$(=Date(Max(AddMonths([Pay Mth], -2))))'}>} Amount)
Jun
Sum({$<[Pay Mth]={'$(=Date(Max(AddMonths([Pay Mth], -1))))'}>} Amount)
Sunny, I still got the same result,
- May=0
- June=0
My bad, I did not see the formatting applied to Pay Mth in the script
Try this:
DATA:
NoConcatenate
LOAD * Inline [
Name,Segment,PayDate,Amount
Acc 1,New,42502,1000
Acc 2,Used,42511,50000
Acc 3,Others,42524,17263.22
Acc 4,New,42536,16036.2
Acc 5,New,42546,2500
Acc 6,New,42550,14000
Acc 7,Others,42551,13322.58
Acc 8,Used,42559,1523
Acc 9,Used,42559,1125
Acc 10,Others,42561,18326
Acc 11,New,42564,562.3
Acc 12,Others,42200,17.13
];
Test:
NoConcatenate
LOAD Name,
Segment,
Date(PayDate,'DD/MM/YYYY') as [Pay Date],
Amount
Resident DATA;
Left Join (Test)
LOAD Name,
Date(Floor(MonthEnd([Pay Date])),'MMM-YYYY') as [Pay Mth]
Resident Test;
DROP Tables DATA;
Three Expressions:
May: =Sum({$<[Pay Mth]={"$(=Date(AddMonths(Max([Pay Mth]), -2), 'MMM-YYYY'))"}>} Amount)
June: =Sum({$<[Pay Mth]={"$(=Date(AddMonths(Max([Pay Mth]), -1), 'MMM-YYYY'))"}>} Amount)
July: =Sum({$<[Pay Mth]={'$(=max([Pay Mth]))'}>} Amount)
Great!!! At last I got the solution.
Thanks Sunny for your assistant.
GR8
No problem at all. I am glad I was able to help
Best,
Sunny