Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: Reuse Set Analysis Result

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

treborscottnam

Contributor III

2021-06-09
02:15 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Reuse Set Analysis Result

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!

1 Solution

Accepted Solutions

sunny_talwar

MVP

2021-06-10
06:41 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

In that case, try this

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

542 Views

6 Replies

sunny_talwar

MVP

2021-06-09
03:20 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Looks like you need the TOTAL Qualifier in your expression

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

582 Views

treborscottnam

Contributor III

2021-06-09
10:30 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

564 Views

sunny_talwar

MVP

2021-06-10
06:41 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

In that case, try this

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

543 Views

treborscottnam

Contributor III

2021-06-10
11:51 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

522 Views

sunny_talwar

MVP

2021-06-10
12:08 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

516 Views

treborscottnam

Contributor III

2021-06-10
12:17 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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