7 Replies Latest reply: Apr 27, 2018 3:33 AM by Quy Nguyen

# Set analysis by field in other table

Hi All,

I have a simple application with below data model:

I want to create a simple table show: EmployeeID and Sum(Amount) based on TradeDate selection.

For each Employee: Sum of Amount should be calculated from Min(TradeDate) to Max(TradeDate) in selection, but if Min(TradeDate) < WorkInDate (the day employee starts to work), Sum of Amount should be calculated from WorkInDate to Max(TradeDate) and all the amount from Min(TradeDate) to WorkInDate should be remove from calculation.

I used this expression but got wrong result:

I also attach here qvf and data sample files. Please help me to solve it. Thank you.

Bests,

Quy

• ###### Re: Set analysis by field in other table

Hi

may be you need to write  Min(WorkInDate) instead of Max(WorkInDate):

try below.

• ###### Re: Set analysis by field in other table

Hi Aruna,

Thanks for your reply. Actually, one employee has only 1 WorkInDate. With the expression above, it gets the Min (or Max) WorkInDate of all Employees and apply that one value for all, leads to incorrect result.

Best.

• ###### Re: Set analysis by field in other table

Hi Quy, set analysis is applied before the rows are calculated, so you can't apply it in row-row basis, the expression will be expanded as:

And this expressio is applied to all rows.

I don't know if employees has many different WorkInDate dates in real data -but it looks like it will be-, in that case maybe you need intervalmatch to set a flag in a table that tells in which TradeDate the employee was active or not (a binary flag), and use this field in set analysis.

• ###### Re: Set analysis by field in other table

If there is only one working date per employee you can create the flag in script foings joins and the comparison between dates to set the flag to use in set analysis:

Join Broker with Link table to have both dates in the same table and create a field in Link table like:

Expression can be:

SUM({<isWorkedIndexKey={1}>}Amount)

• ###### Re: Set analysis by field in other table

Hi Ruben,

Thanks for your answer. I know it can be solve easily if i change the data model. I am looking for a solution by set analysis, but as you said it seem can not be solve. Could you please explain it in more detail, is it a limitation of Qlik?

Best,

Quy

• ###### Re: Set analysis by field in other table

Hi Quy, you can change the expression to filter by the unique key, not the dates: