Show last 12 months sales by month,cust in 1 line - load script
Good day all you smart people!
I'm struggling and really need your help. I'm trying to prepare my tables in order to apply budget trends, weird industry... we do weird things. Basically, what I need is based on the "Invoice_Month" (Which will become my calendar month) I need the current month's sales marked as "Month 1", last month's sales marked as "Month 2" all the way to a "Month 13+"
So my current table looks something like this:
cust_code
Main_Network
Invoice_Month
Calc_Sales
CUST01
Green
2023-01
5
CUST01
Green
2023-02
10
CUST01
Green
2023-03
25
CUST01
Green
2023-04
50
CUST01
Green
2023-05
100
But I now need to get it to look like this (going on towards Month 13+... but you get the idea):
cust_code
Main_Network
Invoice_Month
Month 1
Month 2
Month 3
Month 4
Month 5
CUST01
Green
2023-01
5
CUST01
Green
2023-02
10
5
CUST01
Green
2023-03
25
10
5
CUST01
Green
2023-04
50
25
10
5
CUST01
Green
2023-05
100
50
25
10
5
The code I came up with looks like this... but it is not working and yelling at me about Syntax and missing/misplaced FROM
NoConcatenate
Full_Sales:
Load
cust_code,
Main_Network,
Invoice_Month,
Sum(Calc_Sales) as Calc_Sales
Resident Full_Sales_Temp
Group by cust_code,
Main_Network,
Invoice_Month;
Drop Table Full_Sales_Temp;
NoConcatenate
Sales_for_Trends:
Load Distinct
cust_code,
Main_Network,
Invoice_Month
Resident Full_Sales;
For i = 1 to 12
Let vSalesIndex = FieldIndex('Calc_Sales',Invoice_Month);
Let vSalesVal = FieldValue('Calc_Sales', $(vSalesIndex));
let vFieldmonth = 'Month ' & $(i);
let vSinceMonth = Date(AddMonths(MonthEnd(Invoice_Month),-$(i)+1),'YYYY-MM');
Left Join (Sales_for_Trends)
LOAD
cust_code,
Main_Network,
Invoice_Month,
$(vSalesVal) as [$(vFieldmonth)]
Resident Full_Sales
Where Invoice_Month = '$(vSinceMonth)';
NEXT
Drop Table Full_Sales
I'm going grey... I don't know anymore... haven't even brought in the 'Month 12+' since I couldn't even get this to work PLEASE HELP?
