Creating Previous Year Sales Field in Qlik Sense Script

[sachin1]

I have two fields: Date and Sales. I want to create an additional field for Previous Year Sales in the script, as I need it to calculate growth in a line chart.

Currently, I do not have the Previous Year Sales field, which is why I'm unable to calculate growth. I have tried using Mapping, Previous, and Peek functions, which work in QlikView, but they are not working as expected in Qlik Sense.

Kindly help me create a new field for Previous Year Sales in the script.

I am expecting below  output:

Date Sales Previous Year Sales
2022-04 483
2022-05 648
2022-06 473
2022-07 648
2022-08 643
2022-09 675
2022-10 707
2022-11 739
2022-12 771
2023-01 803
2023-02 835
2023-03 867
2023-04 899 483
2023-05 931 648
2023-06 963 473
2023-07 995 648
2023-08 1027 643
2023-09 1059 675
2023-10 1091 707
2023-11 1123 739
2023-12 1155 771
2024-01 1187 803
2024-02 1219 835
2024-03 1251 867
2024-04 1283 899
2024-05 1315 931
2024-06 1347 963
2024-07 1379 995
2024-08 1411 1027
2024-09 1443 1059
2024-10 1475 1091
2024-11 1507 1123
2024-12 1539 1155
2025-01 1571 1187
2025-02 1603 1219
2025-03 1635 1251
2025-04 1667 1283
2025-05 1699 1315

I want year wise growth..

[its_anandrjs · Accepted solution]

Hi @sachin1 ,

You have to create the calculated column in load script by using Addmonths function to get previous year sales data next to current month data column.

Reagrds,

Anand

[ali_hijazi]

Hello
I did it as follows and I got the result you want:

fact:

Load *

,num(month(Date)) as month_num

    ,year(Date) as year

    ;

Load 

monthname(MakeDate(Left(Date,4),Right(Date,2))) as Date

    ,Sales

;

Load * Inline [

Date, Sales 

2022-04, 483

2022-05, 648

2022-06, 473

2022-07, 648

2022-08, 643

2022-09, 675

2022-10, 707

2022-11, 739

2022-12, 771

2023-01, 803

2023-02, 835

2023-03, 867

2023-04, 899 

2023-05, 931 

2023-06, 963 

2023-07, 995 

2023-08, 1027 

2023-09, 1059 

2023-10, 1091 

2023-11, 1123 

2023-12, 1155 

2024-01, 1187 

2024-02, 1219 

2024-03, 1251 

2024-04, 1283 

2024-05, 1315 

2024-06, 1347 

2024-07, 1379 

2024-08, 1411 

2024-09, 1443 

2024-10, 1475 

2024-11, 1507 

2024-12, 1539 

2025-01, 1571 

2025-02, 1603 

2025-03, 1635 

2025-04, 1667 

2025-05, 1699 

];

fact2:

NoConcatenate 

Load *

,Previous(Sales) as prev_year_sales;

Load * Resident fact

Order by month_num, year;

drop table fact;

[sachin1]

I used the data model below and got the desired output.

Sales:

Load *,

From SalesTable;

Concatenate(Sales)

PreviousYearSales:

LOAD

Date,

AddMonths( Date(Date#(Date,'YYYY-MM'),'DD/MM/YYYY'), 12) as MONTH_YEAR,

'Prev' as PrevFlag,

Sales as PreviousYearSales

RESIDENT Sales;