growth rates in Qlik Sense

[jleefjcapital]

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 ?   

Date	Alabama Jobs	Period over period growth rate
10.2015	159
11.2015	200	26%
12.2015	130	-35%
1.2016	250	92%
2.2016	140	-44%
3.2016	110	-21%
4.2016	100	-9%
5.2016	100	0%
6.2016	100	0%
7.2016	180	80%
8.2016	340	89%
9.2016	249	-27%
10.2016	200	-20%
11.2016	242	21%

[jleefjcapital]

Here you go.  This file is a bit smaller.

[Gysbert_Wassenaar]

Ok, I see a line chart with Date as dimension. What exactly do you expect to see if you add this expression to that line chart?

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

The sum over Sep2016 can only be displayed in september 2016 in a chart with date as x-axis dimension. And the sum of Sep2015 can only be displayed in september 2015. That means it's impossible to display the division of the two in the line chart. There's no date on the x-axis where both sums both exist.

[jleefjcapital]

I just realized that the dates were hard-coded.  You're right.   Is there a way to generate a dynamic growth series? in other words, I would like to create a new field with the period growth rates. 

Thank you,

Jessica 

[Gysbert_Wassenaar]

Yes, using an AsOf table. See this post Re: growth rates in Qlik Sense‌ and the next one.

[jleefjcapital]

Hi Gysbert,

I loaded the Asof Table referencing the Master Calendar.   I get an error message stating the field <Year-Month> is not found. 

Year and Month are well defined in the Master Calendar so I'm not certain why it's not working. 

Thank you,

Jessica

QuartersMap:

MAPPING LOAD

rowno() AS Month,

'Q' & Ceil (rowno()/3) AS Quarter

AUTOGENERATE (12);

Temp:

LOAD

min(DATE) AS minDate,

max(DATE) AS maxDate

Resident BLS_data;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

DATE($(varMinDate) + IterNo()-1) AS TempDate

AutoGenerate 1 While ($(varMinDate) + IterNo() -1) <= $(varMaxDate);

MasterCalendar:

LOAD

    TempDate AS DATE,

    Week(TempDate) AS Week,

    Year(TempDate) AS Year,

    Month(TempDate) AS Month,

    Year(TempDate)*100+Month(TempDate) AS MonthYear,

    Day(TempDate) AS Day,

    ApplyMap('QuartersMap', month(TempDate), Null()) AS Quarter,

    Year(TempDate)&ApplyMap('QuartersMap', month(TempDate), Null()) AS QuarterYear,

    Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) AS WeekYear,

    WeekDay(TempDate) AS WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

//////////////////////////////////////////////////////////////////  CREATION OF AS-OF TABLE ////////////////////////////////////////////////////

// ======== Create a list of distinct Months ========

tmpAsOfCalendar:

Load distinct

  [Year-Month]

Resident MasterCalendar

  ;

// ======== Cartesian product with itself ========

Join (tmpAsOfCalendar)

Load

  [Year-Month] as AsOfMonth

Resident

  tmpAsOfCalendar

    ;

// ======== Reload, filter and calculate additional fields ========

[As-Of Calendar]:

Load

  [Year-Month],

  AsOfMonth,

  Round((AsOfMonth-[Year-Month])*12/365.2425) as MonthDiff,

    Year(AsOfMonth)-Year([Year-Month]) as YearDiff

Resident

  tmpAsOfCalendar

Where

  AsOfMonth >= [Year-Month]

    ;

Drop Table tmpAsOfCalendar;

[Gysbert_Wassenaar]

Oh, ok [Year-Month] is not a construction of the two fields Year and Month. It's a field by itself and it needs to be created first before it can be used in the AsOf calendar.

Your script creates a field MonthYear:

Year(TempDate)*100+Month(TempDate) AS MonthYear,

You're also creating it as a field that's not a date which will cause problems later if used for calculating the MonthDiff and YearDiff fields which expect dates in the AsOfMonth and [Year-Month] fields. So replace the line that creates MonthYear with:

Date(TempDate,'YYYY-MM') AS [Year-Month],

You can change the date format YYYY-MM to something else if you like. You don't need MonthYear afaict, but if you want to keep it then don't replace that line but add the other one below it so the [Year-Month] field will be created as well.

[jleefjcapital]

It worked! To graph the y/y changes which date should I use?  Date, MonthYear, or Asofdate? 

Thank you, Jes

[Gysbert_Wassenaar]

You use the AsOfMonth field as dimension in your chart. See this blog post: The As-Of Table

[jleefjcapital]

Hi Gysbert,

I followed all of the steps, and it does output the dates.  I'm not able to attach the QVF, because it's too large and it takes 4hrs to load the data, but I'm hoping a screenshot would be sufficient. 

Here are the fields I've created: 

(1) BLS_jobs_state last 12 mos:    Sum({<MonthDiff={12}>}BLS_jobs_state)

(2) BLS_jobs_state_curr month:     Sum({<MonthDiff={0}>}BLS_jobs_state)

(3) BLS_jobs_state, yy%:              Sum({<MonthDiff={0}>}BLS_jobs_state)/Sum({<MonthDiff={12}>}BLS_jobs_state) - 1

I'm not sure what I'm doing incorrectly.  I made sure to select one state, which is a dimension for this series, and at least two years. 

Thank you,

Jessica

[Gysbert_Wassenaar]

Try creating an AsOfYear field in your AsOf table: Year(AsOfMonth) as AsOfYear

Then use the AsOfYear field to select the years.