topic Re: Last 3 month sales value in Script in QlikView

Last 3 month sales value in Script

Anonymous — Thu, 18 Oct 2018 18:51:31 GMT

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

Re: Last 3 month sales value in Script

nasirsaikh — Fri, 19 Oct 2018 10:15:25 GMT

Try this.

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

Re: Last 3 month sales value in Script

Anonymous — Fri, 19 Oct 2018 12:47:39 GMT

I need the logic in the script not the set analysis

Re: Last 3 month sales value in Script

dplr-rn — Fri, 19 Oct 2018 13:45:21 GMT

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

The As-Of Table

Re: Last 3 month sales value in Script

Anonymous — Fri, 19 Oct 2018 13:48:45 GMT

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

Re: Last 3 month sales value in Script

chrismarlow — Fri, 19 Oct 2018 13:54:57 GMT

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;

Re: Last 3 month sales value in Script

sergio0592 — Fri, 19 Oct 2018 14:24:31 GMT

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)

Re: Last 3 month sales value in Script

Anonymous — Fri, 19 Oct 2018 14:39:43 GMT

This works great.

Thanks

Re: Last 3 month sales value in Script

dplr-rn — Fri, 19 Oct 2018 14:40:22 GMT

nicely done

Re: Last 3 month sales value in Script

chrismarlow — Fri, 19 Oct 2018 14:43:47 GMT

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

Re: Last 3 month sales value in Script

renuka_sasikumar — Thu, 08 Aug 2019 05:47:17 GMT

Hello, My requirement is the same, but here I have

CALMONTH&'-'&CUSTOMER&'-'&MATERIAL AS SalesKey

Can you help me with the script.

Thanks in advance