Get the last year sales using script level.

[mcaahmad93]

Hi Team,

I have sample sales data based on that i need to get the last year sales.

attached the sample excel file

I am using the below script to get this done but not coming properly. Please suggest me.

Year_data:
LOAD Period,
Sales,
Year(Period) as Period_Year

FROM
[Last Year data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Previous:
Load
Period,
Sales,
Period_Year,
if(Period_Year<>Previous(Period_Year),Sales,0) as Last_Year_Sales
Resident Year_data;
//If(Period_Year = Previous(Period_Year), Alt(Peek('Sales'), 0), 0) as Last_Year_Sales

DROP table Year_data;

Output should be like this:

Period	Sales	Last Year Sales
1-Jan-18	1	0
1-Feb-18	2	0
1-Mar-18	3	0
1-Apr-18	4	0
1-May-18	5	0
1-Jun-18	6	0
1-Jul-18	7	0
1-Aug-18	8	0
1-Sep-18	9	0
1-Oct-18	10	0
1-Nov-18	11	0
1-Dec-18	12	0
1-Jan-19	13	1
1-Feb-19	14	2
1-Mar-19	15	3
1-Apr-19	16	4
1-May-19	17	5
1-Jun-19	18	6
1-Jul-19	19	7
1-Aug-19	20	8
1-Sep-19	21	9
1-Oct-19	22	10
1-Nov-19	23	11
1-Dec-19	24	12

Regards,

Irshad Ansari

[asinha1991]

are you sure its ordered in original dataset? otherwise use peek with order by

Previous:
Load
Period,
Sales,
Period_Year,
if(Period_Year<>peek(Period_Year),Sales,0) as Last_Year_Sales
Resident Year_data order by  Period_Year;

[mcaahmad93]

Thank you @asinha1991  for your valuable time.

i tried your script but value is not coming expected level.

Please find the attached data source based on that i need last year sales data.

Regards,

Irshad Ansari

[asinha1991 · Accepted solution]

now I understand what you want, do this

Previous:
Load
Period,
Sales,
Period_Year,
if(month(Period)=month(peek(Period)),peek(Sales),0) as Last_Year_Sales
Resident Year_data order by  month(Period),Period_Year;

[StarinieriG]

Hi,

you could try this instead

OUTPUT:
LOAD Period,
Sales,
Year(Period)-1 & Month(Period) as Last_Year
FROM
[Last Year data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join
LOAD
Year(Period) & Month(Period) as Last_Year,
Sales as Last_Year_Sales
Resident OUTPUT;

[mcaahmad93]

Thank you @asinha1991  now its working perfectly.

[mcaahmad93]

Thank you @StarinieriG  for you valuable time.

Regards,

Irshad Ansari