calculate yoy growth in data load editor

[Swathi]

i need to calculate year over year growth in data load editor

i have fields volume, tkt_year , customer fields and need to cm yoy growth % field in backend data load editor. 

yoycm= (currentyearcm-previousyear cm/previous year cm) for each customer., how to to do that.

[Scotchy]

A possible solution ...

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:

1. Load Your Data: Ensure that you have your data loaded with the fields volume, tkt_year, and customer.

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

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

sqlCopy code

// Assuming tkt_year is the field for the transaction year and 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] GROUP BY 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.

[Rohan]

Hi Swathi,

Please share a sample of the basic fields that you have in the table, & also at which level are you supposed to calculate the Growth ?

Regards,

Rohan.