To calculate the Year-over-Year (YoY) growth in Qlik Sense within the Data Load Editor, you will need to use a combination of set analysis and aggregation functions to compare the current year's values with the previous year's values for each customer. The general formula for YoY growth is as you've described:
YoY Growth=(Current Year Value−Previous Year ValuePrevious Year Value)×100YoY Growth=(Previous Year ValueCurrent Year Value−Previous Year Value)×100
Here's a step-by-step approach to create a field for YoY growth in Qlik Sense:
Load Your Data: Ensure that you have your data loaded with the fields volume, tkt_year, and customer.
Create Variables for Current and Previous Year: If you don't already have them, you might need to create variables to dynamically determine the current and previous year based on your data. This can be done using Qlik Sense's date functions.
Calculate YoY Growth for Each Customer: You will need to calculate the YoY growth for each customer. This involves calculating the sum of volume for the current year and the previous year for each customer, and then applying the YoY formula.
Here's an example script to illustrate this:
sql
// Assuming tkt_year is the field for the transaction yearand volume is the sales volume
LOAD
customer,
tkt_year,
volume,
// Other fields you might need
...
;
LOAD
*,
If(tkt_year =Year(Today()),
(Sum({<tkt_year = {$(=Year(Today()))}>} volume) -Sum({<tkt_year = {$(=Year(Today())-1)}>} volume)) /Sum({<tkt_year = {$(=Year(Today())-1)}>} volume),
null()) as YoYcm
FROM [YourDataSource]
GROUPBY customer, tkt_year;
In this script:
Year(Today()) finds the current year.
Year(Today())-1 finds the previous year.
Sum({<tkt_year = {$(=Year(Today()))}>} volume) calculates the total volume for the current year.
Sum({<tkt_year = {$(=Year(Today())-1)}>} volume) calculates the total volume for the previous year.
The If statement ensures that YoYcm is calculated only for the current year.
