# 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
Did you mean:
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:

 Year Month Headcount Terminations First Day of Year 2015 1 5000 635 4950 2015 2 5084 457 2015 3 5187 565 2015 4 4922 240 2015 5 5352 502 2015 6 5250 680 2015 7 5270 275 2015 8 5218 196 2015 9 4800 544 2015 10 5875 646 2015 11 5466 420 2015 12 4927 179 2016 1 5855 360 5560 2016 2 5887 310 2016 3 5464 420 2016 4 5984 433 2016 5 5464 356 2016 6 5123 411 2016 7 5649 345 2016 8 5345 353 2016 9 5935 304 2016 10 5140 313 2016 11 5456 383 2016 12 5949 327

I want it to look like is this:

 Year Month Headcount Terminations First Day of Year 2015 1 5000 635 4950 2015 2 5084 457 4950 2015 3 5187 565 4950 2015 4 4922 240 4950 2015 5 5352 502 4950 2015 6 5250 680 4950 2015 7 5270 275 4950 2015 8 5218 196 4950 2015 9 4800 544 4950 2015 10 5875 646 4950 2015 11 5466 420 4950 2015 12 4927 179 4950 2016 1 5855 360 5560 2016 2 5887 310 5560 2016 3 5464 420 5560 2016 4 5984 433 5560 2016 5 5464 356 5560 2016 6 5123 411 5560 2016 7 5649 345 5560 2016 8 5345 353 5560 2016 9 5935 304 5560 2016 10 5140 313 5560 2016 11 5456 383 5560 2016 12 5949 327 5560

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:

 Year Month Headcount Terminations First Day of Year Turnover 2015 1 5000 635 4950 3.2% 2015 2 5084 457 4950 5.4% 2015 3 5187 565 4950 8.2% 2015 4 4922 240 4950 9.6% 2015 5 5352 502 4950 11.6% 2015 6 5250 680 4950 15.1% 2015 7 5270 275 4950 16.4% 2015 8 5218 196 4950 17.5% 2015 9 4800 544 4950 21.0% 2015 10 5875 646 4950 21.9% 2015 11 5466 420 4950 24.8% 2015 12 4927 179 4950 27.0% 2016 1 5855 360 5560 1.6% 2016 2 5887 310 5560 2.9% 2016 3 5464 420 5560 4.9% 2016 4 5984 433 5560 6.6% 2016 5 5464 356 5560 8.5% 2016 6 5123 411 5560 10.7% 2016 7 5649 345 5560 11.8% 2016 8 5345 353 5560 13.7% 2016 9 5935 304 5560 14.3% 2016 10 5140 313 5560 16.8% 2016 11 5456 383 5560 18.1% 2016 12 5949 327 5560 18.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)

• ### Other

0 Replies
Community Browser