Reuse Set Analysis Result

[treborscottnam]

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.

Where I am stuck is that it only shows up in the first month, in the table.   I just need to get the First Day of Year number. That is so I can use this calculation:

=YTD Terminations / (First Day of Year + Month Headcount)/2

 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.

I appreciate the advice!

[treborscottnam]

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.

Where I am stuck is that it only shows up in the first month, in the table.   I just need to get the First Day of Year number. That is so I can use this calculation:

=YTD Terminations / (First Day of Year + Month Headcount)/2

 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.

I appreciate the advice!

[sunny_talwar]

Looks like you need the TOTAL Qualifier in your expression

=Sum(TOTAL {<IsFirstDay={"Y"}, Month = {"1"}>} Active)

[treborscottnam]

Hi sunny_talwar,

I really appreciate the answer. I tried that, but it didn't work for me. 

The problem is, I neglected to mention that I have multiple years. So the TOTAL did its job and summed up the years, but I need the total just for the year in which those months fall.

For example, what I'd like to see is a table like I've attached (I tried inserting a table, but kept getting errors that wouldn't allow me to post). Using TOTAL gave me 10,150, in the First Day of the Year column.

I hope that fills in what I neglected to say originally.

Thank you!

[sunny_talwar · Accepted solution]

In that case, try this

=Sum(TOTAL <Year> {<IsFirstDay={"Y"}, Month = {"1"}>} Active)

[treborscottnam]

Thank you so much! That is exactly what I was looking for and it was a huge help.

I thought I had a basic understanding of Set Analysis, but it appears I still have a lot more to learn.  

[sunny_talwar]

Well this is not really set analysis. Read more about Total Qualifier here -> Defining the aggregation scope 

[treborscottnam]

Well, that proves even more how much I have to learn! 😁