topic Get the last year sales using script level. in QlikView

Get the last year sales using script level.

mcaahmad93 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Get the last year sales using script level.

asinha1991 — Tue, 01 Oct 2019 06:58:37 GMT

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;

Re: Get the last year sales using script level.

mcaahmad93 — Tue, 01 Oct 2019 07:12:03 GMT

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

Re: Get the last year sales using script level.

asinha1991 — Tue, 01 Oct 2019 08:29:40 GMT

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;

Re: Get the last year sales using script level.

StarinieriG — Tue, 01 Oct 2019 08:44:37 GMT

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;

Re: Get the last year sales using script level.

mcaahmad93 — Tue, 01 Oct 2019 08:57:26 GMT

Thank you @asinha1991 now its working perfectly.

Re: Get the last year sales using script level.

mcaahmad93 — Tue, 01 Oct 2019 09:07:00 GMT

Thank you @StarinieriG for you valuable time.

Regards,

Irshad Ansari