calculate yoy growth in data load editor

Swathi — Fri, 15 Nov 2024 21:07:40 GMT

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.

Re: calculate yoy growth in data load editor

Scotchy — Fri, 12 Jan 2024 02:52:20 GMT

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 = (Previous Year Value Current Year Value - Previous Year Value) \times 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 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.

Re: calculate yoy growth in data load editor

Rohan — Fri, 12 Jan 2024 06:47:27 GMT

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.

topic Re: calculate yoy growth in data load editor in App Development

calculate yoy growth in data load editor

Re: calculate yoy growth in data load editor

Re: calculate yoy growth in data load editor