How to get sum for items less than 1 month from maximum month in expression

[mohdhaniff]

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

[sunny_talwar]

Have you tried this?

=Sum({$<[Pay Mth]={'$(=Max(AddMonths([Pay Mth], -1)))'}>} Amount) -> June

=Sum({$<[Pay Mth]={'$(=Max(AddMonths([Pay Mth], -2)))'}>} Amount) -> May

[sunny_talwar]

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)

[mohdhaniff]

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.

[sunny_talwar]

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)

[mohdhaniff]

Sunny, I still got the same result,

- May=0

- June=0

[sunny_talwar · Accepted solution]

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)

[mohdhaniff]

Great!!! At last I got the solution.

Thanks Sunny for your assistant.

[vikasmahajan]

GR8

[sunny_talwar]

No problem at all. I am glad I was able to help

Best,

Sunny