topic Re: Creating Previous Year Sales Field in Qlik Sense Script in App Development

Creating Previous Year Sales Field in Qlik Sense Script

sachin1 — Thu, 19 Jun 2025 05:38:56 GMT

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..

Re: Creating Previous Year Sales Field in Qlik Sense Script

its_anandrjs — Thu, 19 Jun 2025 09:01:08 GMT

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

Re: Creating Previous Year Sales Field in Qlik Sense Script

ali_hijazi — Fri, 20 Jun 2025 14:46:05 GMT

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;

Re: Creating Previous Year Sales Field in Qlik Sense Script

sachin1 — Wed, 23 Jul 2025 07:20:24 GMT

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;