Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;