topic Re: Reuse Set Analysis Result in QlikView

Reuse Set Analysis Result

treborscottnam — Wed, 09 Jun 2021 18:15:31 GMT

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!

Re: Reuse Set Analysis Result

sunny_talwar — Wed, 09 Jun 2021 19:20:06 GMT

Looks like you need the TOTAL Qualifier in your expression

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

Re: Reuse Set Analysis Result

treborscottnam — Thu, 10 Jun 2021 02:30:55 GMT

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!

Re: Reuse Set Analysis Result

sunny_talwar — Thu, 10 Jun 2021 10:41:41 GMT

In that case, try this

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

Re: Reuse Set Analysis Result

treborscottnam — Thu, 10 Jun 2021 15:51:54 GMT

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.

Re: Reuse Set Analysis Result

sunny_talwar — Thu, 10 Jun 2021 16:08:48 GMT

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

Re: Reuse Set Analysis Result

treborscottnam — Thu, 10 Jun 2021 16:17:23 GMT

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