Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
treborscottnam
Contributor III
Contributor III

Help with understanding set analysis and using the result in a table

Hello,

I have a table that for each month shows what the ending of the month headcount is. It uses this expression:
Month Headcount = Sum( {<IsLastDay={"Y"}>} Active)

In order to do a calculation (turnover) I need to also get the first day of the year headcount and use that number in each row of the table.
I can get the first day of the month, for the first month number with this expression:
First Day of Year =Sum( {<IsFirstDay={"Y"}, Month = {"1"}>} Active)

My question is:  How can I repeat that first day of the month in the table or get that result in each row's calculation. The final result would be using this expression: =YTD Terminations / (First Day of Year + Month Headcount)/2

Where I am stuck is that it only shows up in the first month. Here's what the table looks like now:

YearMonthHeadcountTerminationsFirst Day of Year
2015150006354950
201525084457 
201535187565 
201544922240 
201555352502 
201565250680 
201575270275 
201585218196 
201594800544 
2015105875646 
2015115466420 
2015124927179 
2016158553605560
201625887310 
201635464420 
201645984433 
201655464356 
201665123411 
201675649345 
201685345353 
201695935304 
2016105140313 
2016115456383 
2016125949327 

 

I want it to look like is this:

YearMonthHeadcountTerminationsFirst Day of Year
2015150006354950
2015250844574950
2015351875654950
2015449222404950
2015553525024950
2015652506804950
2015752702754950
2015852181964950
2015948005444950
20151058756464950
20151154664204950
20151249271794950
2016158553605560
2016258873105560
2016354644205560
2016459844335560
2016554643565560
2016651234115560
2016756493455560
2016853453535560
2016959353045560
20161051403135560
20161154563835560
20161259493275560


It doesn't have to actually look like that, I just need to get the First Day of Year number. That is so, as I said above, I can use this calculation:
=YTD Terminations / (First Day of Year + Month Headcount)/2

So the end result would be this:

YearMonthHeadcountTerminationsFirst Day of YearTurnover
20151500063549503.2%
20152508445749505.4%
20153518756549508.2%
20154492224049509.6%
201555352502495011.6%
201565250680495015.1%
201575270275495016.4%
201585218196495017.5%
201594800544495021.0%
2015105875646495021.9%
2015115466420495024.8%
2015124927179495027.0%
20161585536055601.6%
20162588731055602.9%
20163546442055604.9%
20164598443355606.6%
20165546435655608.5%
201665123411556010.7%
201675649345556011.8%
201685345353556013.7%
201695935304556014.3%
2016105140313556016.8%
2016115456383556018.1%
2016125949327556018.7%

 

I had tried some different approaches such as using variables and trying out using an equal sign in the expression, and sometime not. I'm still a bit fuzzy on when the expression calculates based on if there is an equal sign or not in the expression.

Thank you for the help!

Labels (2)
0 Replies