Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Last 3 month sales value in Script

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

1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

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;

View solution in original post

10 Replies
nasirsaikh
Creator
Creator

Try this.

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

Anonymous
Not applicable
Author

I need the logic in the script not the set analysis

dplr-rn
Partner - Master III
Partner - Master III

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

The As-Of Table

Anonymous
Not applicable
Author

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

chrismarlow
Specialist II
Specialist II

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
Specialist III
Specialist III

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
Not applicable
Author

This works great.

Thanks

dplr-rn
Partner - Master III
Partner - Master III

nicely done

chrismarlow
Specialist II
Specialist II

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