topic Re: Linking two dates in Set Analysis in QlikView

Linking two dates in Set Analysis

Mon, 21 Mar 2016 04:55:50 GMT

I'm trying to link two dates in a Set Analysis. When I do it using an If-statement, it works just fine. But I understand that If-statements are not the ideal solution as they are slow.

How can I achieve that with a Set-Analysis instead?

My expression currently looks like this:

=1/Avg(If(FleetDate=FlightDate, MXRatio))

I tried the following set analysis:

=1/Avg({$< _UtilizationKey = {"=(FleetDate=FlightDate)"} >} MXRatio)

The utilizationkey is just a numeric key field (rowno()) in the same table as the MXRatio, as I read, that it's not possible to use the searched dimension cannot be in the boolean condition.

Re: Linking two dates in Set Analysis

Gysbert_Wassenaar — Mon, 21 Mar 2016 08:06:16 GMT

If there's only one FleetDate - FlightDate combination per _UtilizationKey value then it should work. If not, then you'll need an if statement. See this document for more information: set_analysis_intra-record.qvw

Re: Linking two dates in Set Analysis

Mon, 21 Mar 2016 08:27:41 GMT

Thanks for your reply.

There is one _UtilizationKey per records, as I create it during the load with a RowNo() function.

The FleetDate field is currently holding only one date (today's) and in the FlightDate there are multiple dates, ranging from 8.3. - 21.3.

So yes, there should be a combination of FleetDate to FlightDate