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:
| Factory | Target | StartDate | EndDate |
|---|
| A | 15 | 2017-10-01 | 2017-12-31 |
| A | 25 | 2018-01-01 | NULL |
| B | 17 | 2017-10-01 | NULL |
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:
Min({$<Date={"$(=max(Date))"}>} Target)
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:
if(avgTaskTime < Min({$<Date={"$(=max(Date))"}>} Target), 'green', 'red')
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!
