Last 3 month sales value in Script

[Anonymous]

All Experts,

Need your help in calculating the last 3 full months sum in the script not in the front end.

There may be more than one transaction in a month and I want to sum all the previous three months(excluding the current month)

Sample data with the expected output attached for your kind reference

Any help would be much appreciated

Regards

Jeba

[nasirsaikh]

Try this.

Sum({<Cal_Date={">=$(=Num(MonthStart(AddMonths(Max(Cal_Date),-4)))) <=$(=Num(MonthEnd(AddMonths(Max(Cal_Date),-1))))"}>}Sales_Amount)

[Anonymous]

I need the logic in the script not the set analysis

[dplr-rn]

Not exactly what you want but check if below will be of use

The As-Of Table

[Anonymous]

I already tried these options before posting this question and didn't worked

[chrismarlow · Accepted solution]

Jebamalai,

You can try the attached (will also post code below as I have personal edition).

Regards,

Chris.

data:
LOAD * INLINE [
dim, Amount, DateField
A, 65.33, 26-Feb-2018
A, 130.76, 16-Mar-2018
A, 65.52, 03-Apr-2018
A, 130.94, 21-Apr-2018
A, 65.55, 09-May-2018
A, 261.81, 27-May-2018
A, 327.24, 14-Jun-2018
A, 65.59, 02-Jul-2018
A, 392.89, 20-Jul-2018
A, 523.83, 07-Aug-2018
A, 65.59, 25-Aug-2018
A, 131.04, 12-Sep-2018
A, 458.36, 30-Sep-2018
A, 65.62, 18-Oct-2018
];

dates:
Load Distinct
Monthstart(AddMonths(DateField,-3)) as StartDate,
MonthEnd(AddMonths(DateField,-1)) as EndDate,
DateField AS LinkDate
Resident data;

Inner Join IntervalMatch (DateField)
Load
StartDate,
EndDate
Resident dates;

Join (dates)
Load
DateField,
dim,
Amount
Resident data;

datesaggr:
Load
LinkDate,
dim,
Sum(Amount) as ThreeMonth
Resident dates
group by LinkDate, dim;

Left Join (data)
Load
LinkDate as DateField,
dim,
ThreeMonth
resident datesaggr;

drop tables dates, datesaggr;

[sergio0592]

You can just use Monthend and MonthEnd for add or subtract month

Sum({<Cal_Date={">=$(=Num(MonthStart(Max(Cal_Date),-4))) <=$(=Num(MonthEnd(Max(Cal_Date),-1)))"}>}Sales_Amount)

[Anonymous]

This works great.

Thanks

[dplr-rn]

nicely done

[chrismarlow]

Thanks - There may be a neater solution. I'd been on the look out for an intervalmatch challenge for a while .