Set analysis by field in other table

[Quy_Nguyen]

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:

SUM({<TradeDate={">=$(=Date(Max(WorkInDate)))<=$(=Date(Max(TradeDate)))"} >}Amount)

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

Bests,

Quy

[Anonymous]

Hi

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

try below.

=SUM({<TradeDate={">=$(=Date(Min(WorkInDate)))<=$(=Date(Max(TradeDate)))"} >}Amount)

[Quy_Nguyen]

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.

[rubenmarin]

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:

SUM({<TradeDate={">=15/03/2018<=16/03/2018"} >}Amount)

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.

[rubenmarin]

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:

If(TradeDate>=WorkInDate, 1, 0) as isWorkedIndexKey

Expression can be:

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

[Quy_Nguyen]

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

[rubenmarin · Accepted solution]

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

SUM({<AccountIndexKey={"=TradeDate>=WorkInDate"} >}Amount)

[Quy_Nguyen]

Great Ruben! It's a perfect answer. Thank you so much.

Best,

Quy