Qlik Community

Qlik Sense App Development

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

jleefjcapital
Contributor

growth rates in Qlik Sense

Hello,

I would like to create a new variable in Qlik Sense for growth rates.   As an example of what I'm looking for: 

[Alabama Jobs] is the main field that I'm pulling from an external database-- I would like to compute the period over period growth rate as in column three.   There there a way to hard code this in the data editor ?   

   

DateAlabama Jobs Period over period growth rate
10.2015159
11.201520026%
12.2015130-35%
1.201625092%
2.2016140-44%
3.2016110-21%
4.2016100-9%
5.20161000%
6.20161000%
7.201618080%
8.201634089%
9.2016249-27%
10.2016200-20%
11.201624221%
1 Solution

Accepted Solutions
jleefjcapital
Contributor

Re: growth rates in Qlik Sense

I see.  I've attached the QVF ----the measure is already created titled State jobs_yy% change.

a. (Sum({$<MonthYear={'Sep2016'}>}BLS_jobs_state) / Sum({$<MonthYear={'Sep2015'}>}BLS_jobs_state))-1

Thanks.

29 Replies

Re: growth rates in Qlik Sense

Something like this:

Temp:

LOAD

     Date(Date#(Date,'M.YYYY'),'M.YYYY') as Date

     [Alabama Jobs]

FROM

     the_source_data

     ;

Result:

LOAD

     Date,

     [Alabama Jobs],

     Num([Alabama Jobs] / Previous([Alabama Jobs]) - 1, '#%;-#%') as [Period over period growth rate]

RESIDENT

     Temp

ORDER BY

     Date

     ;

DROP TABLE Temp;


talk is cheap, supply exceeds demand
jleefjcapital
Contributor

Re: growth rates in Qlik Sense

What if I wanted to set the growth rate to a year on a monthly occurring series? 

Re: growth rates in Qlik Sense

Then first aggregate the numbers at those levels. But that's something that's usually done in the charts, not the script. Doing those kind of aggregations in the charts gives more flexibility as selections will work nicely with that. Fixing the numbers in the script is less flexible. It's normally only done if necessary for performance reasons.


talk is cheap, supply exceeds demand
jleefjcapital
Contributor

Re: growth rates in Qlik Sense

Are you referring to creating a measure on the front end?  Could you provide an example of how I would aggregate to get year over year growth rates? 

jleefjcapital
Contributor

Re: growth rates in Qlik Sense

So just to clarify what I'm trying to achieve, I'm looking t o compute the growth rate for each month over the same month a year ago. 

So,   Oct2016/Oct2015 - 1

Re: growth rates in Qlik Sense

Yeah, creating a measure. I'm going to assume you have a field called Year and you're using that as dimension in your chart or table. You can use Sum([Alabama Jobs]) to sum up those amounts so you get totals for each year. If you use a simple table then you can use the Above() function to refer to the value of the row above. So Above(Sum([Alabama Jobs])) would return the sum for the previous year. You can then create a measure that divides the two values to calculate the year-on-year variation: Sum([Alabama Jobs]) / Above(Sum([Alabama Jobs])) - 1.

If you sort the Year dimension in descending order you'd need to use the Below() function instead of Above().


talk is cheap, supply exceeds demand
jleefjcapital
Contributor

Re: growth rates in Qlik Sense

For a multiyear series (with monthly frequency), how do we know Sum() will only sum one year?  

Re: growth rates in Qlik Sense

A measure will be aggregated over the values of your dimension (unless you overrule that). So if you use Year as a dimension you will get totals per year value.


talk is cheap, supply exceeds demand

Re: growth rates in Qlik Sense

Ok, that's going to be more complicated. The best way to deal with this kind of problem is to add a so-called As-Of table to your data model. Before we start with that you need to view a video and read two blog posts:

A Beginners' Introduction to Set Analysis (video)

A Primer on Set Analysis

The As-Of Table

The As-Of Table will be used to create a new field that will be used as dimension in the charts and in the set analysis expression that we'll need for the solution to your question. That new field will link to the real Month field so we can select the Month values we're interested in and calculate the value for the months we want to compare, e.g. Oct2016 with Oct2015. We'll need some set analysis expressions to calculate those results.

View the video and read the blog posts and let me know if you don't understand how those things will help us create the solution to your problem.


talk is cheap, supply exceeds demand
Community Browser