topic Re: Quick set analysis question in QlikView

Quick set analysis question

samuel_brierley — Mon, 22 Jun 2015 14:30:37 GMT

Hi all,

below is a formula im using to average the NOx as you can see but im having an issue witht eh set analysis, where I say {$(=num(avg([Speed(Lane 1)]),'#.')), its calculating the average of the whole selected range and not by the dimension, e.g

Time	Speed (Lane 1)	$<[Average speed (km/h)] = {$(=num(avg([Speed(Lane 1)]),'#.'))}>}	What I Want
00:00	50	65	50
00:01	60	65	60
00:02	70	65	70
00:03	80	65	80

avg({$<[Average speed (km/h)] = {$(=num(avg([Speed(Lane 1)]),'#.'))}>} [NOx EF (g/km)])

what I want the formula to do is avg the NOx where the Speed (Lane 1) = Average Speed (km/h) (I cant rename the fields unfortunately)

one possible answer is to add the Time dimension into the formula in the same way I have done with the Speed Field but I couldn't get that to work.

hopefully that is enough info to get you started, I imagine its quite a simple change.

thanks

Re: Quick set analysis question

Peter_Cammaert — Mon, 22 Jun 2015 14:34:26 GMT

Set analysis works before the chart is recalculated. You cannot use it to perform row-by-row evaluations/calculations.

aggr() will be your friend.

Peter

Re: Quick set analysis question

Clever_Anjos — Mon, 22 Jun 2015 14:39:52 GMT

Set Analysis filters are made before calculation of each row.

It´s the way it works by design

Re: Quick set analysis question

hic — Mon, 22 Jun 2015 14:40:33 GMT

No, it's not a just a simple change. The dollar expansion and the Set Analysis selection is made before the chart is calculated.

Set Analysis is never evaluated for each row. You can only use expressions that can result in selections before the chart is evaluated.

Further, I am not quite sure I understand what you want to achieve... In the first row of your chart, the "Speed (Lane 1)" is 50. But then you also a field "Average speed (km/h)". How does this relate to the first field? And how could it ever be different from 50 on the first row?

HIC

Re: Quick set analysis question

samuel_brierley — Mon, 22 Jun 2015 14:44:12 GMT

What im trying to achieve is simply avg the NOx where the [Average speed (km/h)] is equal to the speed In lane 1

avg({$<[Average speed (km/h)] = {$(=num(avg([Speed(Lane 1)]),'#.'))}>} [NOx EF (g/km)])

Re: Quick set analysis question

Peter_Cammaert — Mon, 22 Jun 2015 14:50:51 GMT

Can you please post a file with example source data? That would make it a lot easier to find a solution. Thanks.

Peter

Re: Quick set analysis question

samuel_brierley — Mon, 22 Jun 2015 14:56:06 GMT

Cheers Peter for looking at this,

Re: Quick set analysis question

hic — Mon, 22 Jun 2015 14:56:15 GMT

I understand that you want to calculate the avg NOx using some condition, but I don't understand how [Speed (Lane 1)] relates to [Average speed (km/h)]. Especially since you use [Speed (Lane 1)] as dimension in your chart.

Anyway, posting some data, as Peter suggests, is probably a good idea.

HIC

Re: Quick set analysis question

samuel_brierley — Mon, 22 Jun 2015 15:03:59 GMT

there are two tables, one contains vehicle information inc speed(lane 1,2,3) and vehicle type. the other table contains vehicle type, speed and NOx (the field I want to return).

the 2 tables are linked by the vehicle type so I should be able to return the NOx for each vehicle type at the speed recorded in the first table.

that was my assumption anyway but its proving more difficult than that.