topic Re: set analysis expression comparing fields in App Development

set analysis expression comparing fields

Anonymous — Fri, 02 Feb 2018 18:55:01 GMT

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!

Re: set analysis expression comparing fields

sunny_talwar — Fri, 02 Feb 2018 19:15:56 GMT

Are you using IntervalMatch to associate your date with start and end date?

Re: set analysis expression comparing fields

Anonymous — Fri, 02 Feb 2018 19:39:39 GMT

I am not. Do you think this would solve my problem? If you don't mind, could you explain? I've never heard of IntervalMatch.

Re: set analysis expression comparing fields

sunny_talwar — Fri, 02 Feb 2018 19:41:37 GMT

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)?

For interval match, you can check here

IntervalMatch

Re: set analysis expression comparing fields

Anonymous — Fri, 02 Feb 2018 20:57:29 GMT

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?

Re: set analysis expression comparing fields

sunny_talwar — Mon, 05 Feb 2018 12:31:34 GMT

I think might make life easy, if you are able to share a sample with us and explain as to what exactly is needed here

Re: set analysis expression comparing fields

Anonymous — Mon, 05 Feb 2018 20:04:18 GMT

I've been working on this and I've gotten part of my formula to work. Here's my formula currently:

Min({$<Date={"$(=max({$<Operator=>} TOTAL Date))"}>} Target)/60

The portion that is working is: {"$(=max({$<Operator=>} TOTAL Date))"}

It is returning the maximum date in the data ignoring the maximum date of the operator of each operator in the bar chart.

However, I think I need the Date to the left of the equal sign to do the same. I tried:

Min({$<OperatorName=>} TOTAL Date={"$(=max({$<OperatorName=>} TOTAL Date))"}>} Target)/60 but it doesn't work.

Basically, I want the Date selected and evaluated over the factory's set of data and to ignore the operator.

Re: set analysis expression comparing fields

sunny_talwar — Mon, 05 Feb 2018 20:43:53 GMT

I have no idea what you are trying, but may be try this

Min({$<Date={"$(=max({$<Operator>} Date))"}, Operator>} Target)/60