I am using Qlik Sense. Here is the description of what I am trying to do and the problem I am facing.
In my data I have operators at two factories. The operators do not work every day, so not everyone has the same date for their most recent data. However, the factory has some operators working every day.
When an operator is performing a task the amount of time to complete the task is measured. Each factory has a performance target for completing a task and the target can change over time.
An example of my target data for the factories looks like this:
I need an expression that tells me the target given the following criteria:
1) If one factory is selected, return the most recent Target (relative to the Date Range selection in the app).
2) If two factories are selected, return the lowest of the most recent Targets (relative to the Date Range selection in the app).
Examples of correct results:
- Date Range selection: 2017-12-01 to 2017-12-31, Factory: A. Result: 15.
- Date Range selection: 2017-12-01 to 2017-12-31, Factory: A,B. Result: 15.
- Date Range selection: 2017-12-01 to 2018-01-31, Factory: A,B. Result: 17.
- Date Range selection: 2018-01-01 to 2018-01-31, Factory: A. Result: 25.
I have written an expression which accomplishes this:
Date is every date in the Calendar filter in the app. The target data table above is joined to the data model so that every date for every factory has a target.
The problem I am having is I am using this expression to compare it to each operator's average task time in a bar chart. The dimension in the bar chart is operator, and the measure is average task time. If the operator's average task time is below the target value that comes from the expression then color the operator's bar green, otherwise color it red.
So it looks like this in the chart's color expression:
What's happening is operators who are lower than the target are only getting colored green if they also have task data on the max date selected. What I want is for them to be colored green if they are lower than the target and have data within the date range.
Can someone help with this? My guess is my expression is incorrect but I am not sure, and I haven't been able to figure out a solution.
If you need more information or clarification please ask. Thank you for helping me!
Well my question is how is the target table linked to operator? It seems that target table has start and end date, but not Date field which you used in the set analysis. This Date field might have come from operator's table, but then how the two tables are connected (or are they even connected on anything)?
I understand what you're saying. Thanks for the explanation. We have a table in our data model that does what you're asking. The table has Factory, ShiftDate, Operator, and Target for every date the target was effective. ShiftDate in this table is linked to Date in our calendar table. Does this answer your question? Does it change the way the set analysis should be written?