Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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% |
Here you go. This file is a bit smaller.
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.
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
Yes, using an AsOf table. See this post Re: growth rates in Qlik Sense and the next one.
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;
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.
It worked! To graph the y/y changes which date should I use? Date, MonthYear, or Asofdate?
Thank you, Jes
You use the AsOfMonth field as dimension in your chart. See this blog post: The As-Of Table
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
Try creating an AsOfYear field in your AsOf table: Year(AsOfMonth) as AsOfYear
Then use the AsOfYear field to select the years.