Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
Joakimnn
New Contributor

YoY Growth, data from previous year

Hello Experts, 

I'm in dire need of your assistance with the following issue.

I want to retrieve data from a table to fetch values from previous years and determine the growth YoY.

As of now, the table looks like this:

YearRegionMakeModelSales
2020North AmericaFordFiesta1000
2020JapanNissanAltima1500
2020EuropeHyundaiElantra800
2021North AmericaFordFiesta1100
2021ChinaNissanAltima1600
2021EuropeHyundaiElantra900
2022North AmericaFordFiesta1200
2022ChinaNissanAltima1700
2022EuropeHyundaiElantra1000
2023North AmericaFordFiesta1300
2023ChinaNissanAltima1800
2023EuropeHyundaiElantra1100
2024North AmericaFordFiesta1400
2024ChinaNissanAltima1900
2024EuropeHyundaiElantra1200

 

I need the result to be something along the lines of the following table:

YearRegionMakeModelSalesLast Year Sales
2020North AmericaFordFiesta1000 
2020JapanNissanAltima1500 
2020EuropeHyundaiElantra800 
2021North AmericaFordFiesta11001000
2021ChinaNissanAltima16001500
2021EuropeHyundaiElantra900800
2022North AmericaFordFiesta12001100
2022ChinaNissanAltima17001600
2022EuropeHyundaiElantra1000900
2023North AmericaFordFiesta13001200
2023ChinaNissanAltima18001700
2023EuropeHyundaiElantra11001000
2024North AmericaFordFiesta14001300
2024ChinaNissanAltima19001800
2024EuropeHyundaiElantra12001100

 

I've tried using Above(), and offset the number of rows to get the correct value for each year, however, once I apply filters, the values are incorrect.

The end result would be a line chart, showing YoY growth, that can be filtered by region, make or model.

Thank you!

1 Reply
Partner
Partner

Re: YoY Growth, data from previous year

Hi Joakimnn,

What you need to do is the following:

Table:
Load
 Year &'|'& Region &'|'& Make  &'|'&  Model as %Table
 Year,
 Region,
 Make,
 Model,
 Sales
From [YourSource] (qvd);

Left Join (Table)
Load
 (Year+1) &'|'& Region &'|'& Make  &'|'&  Model as %Table
 Sales
From [YourSource] (qvd);

 

Now you have joined your sales to the table.

Second option is to go for a mapping load and return 0 if there is no sales for the previous year. In the first solution you will get a null().

Jordy

Climber

Work smarter, not harder