This Month values minus Last month values in script

[HermGerm]

Hi,

in a load script, I want to calculate the difference between "this month" sales and "last month" sales, where the "Elementname" is the same.

Tables looks like this:

Data:

Elementname,

Sales,

Date

So written in words something like this:

newData:

LOAD

Elementname,

Sales,

Date,

Sum("where Elementname = Elementname and date = date") - Sum("where Elementname = Elementname and Date=Addmonth(date,-1)") as "prSales"

Example data could be:

AB,100,01.01.2020

AB,50,01.12.2019,

AC,100,01.02.2020

Expected is this:

100-50 (for AB)

100-0 (for AC, since there is no value from previous month)

How to do this?

[Saravanan_Desingh]

Are you looking like this?

tab1:
LOAD *, MonthStart(Date#(Sales_Date,'DD.MM.YYYY')) As  Sales_MS INLINE [
	Elementname, Sales, Sales_Date
    AB, 100, 01.01.2020
    AB, 50, 01.12.2019
    AC, 100, 01.02.2020
];

tab2:
LOAD Elementname, Sales_MS, Sum(Sales) As Sales_Sum
Resident tab1
Group By Elementname, Sales_MS
;

Left Join (tab2)
LOAD Elementname, Count(Sales_MS) As MS_Count
Resident tab1
Group By Elementname
;

tab3:
LOAD Elementname, Sales_MS, If(MS_Count=1,Sales_Sum,If(Elementname=Peek(Elementname),Sales_Sum - Alt(Peek(Sales_Sum),0))) As Sales_Diff,
Sales_Sum , Peek(Sales_Sum) As Sales_Peek
Resident tab2
Order By Elementname, Sales_MS Asc
;

Drop Table tab1, tab2;