topic Re: Set Analysis on Aggregated Average in QlikView

Set Analysis on Aggregated Average

Anonymous — Wed, 23 Jan 2013 19:14:52 GMT

I have the following in a pivot table:

=aggr (nodistinct (round (avg(x),0.01)), a, b, c, d, e, f)

What I want is to add this aggregated average into a secondary pivot table (that has additional dimensions) but I don't want the aggregate to change based on the additional dimensions.

Looking at the Qlikview Help file it states "By default, the aggregation function will aggregate over the set of possible records defined by the selection. An alternative set of records can be defined by a Set Analysis expression".

So is it possible to do a Set Analysis on an aggregated average? If so, how do I go about this?

Thanks

Matt

Re: Set Analysis on Aggregated Average

Anonymous — Wed, 23 Jan 2013 20:59:18 GMT

If you want both to display the same data, irrespective of selection, you will probably need to implement the same set analysis in both functions. Take a look at this presentation for more info on set analysis.

Re: Set Analysis on Aggregated Average

Anonymous — Fri, 25 Jan 2013 12:19:05 GMT

Thanks for the attachment, it was a useful read but I'm still unable to get this working. For testing purposes I removed the second Pivot table and added the Set Analysis as follows:

sum({1} aggr (nodistinct (round (avg(x),0.01)), a, b, c, d, e, f))

This works correctly but as soon as I filter on a, b, c, d, e or f the aggregated average is recalculated.

I used {1} as the document stated this "represents the full set of all records in the application" but perhaps I've misinterpreted?

Any advice greatly appreciated.

Thanks

Matt

Re: Set Analysis on Aggregated Average

Anonymous — Mon, 28 Jan 2013 20:08:28 GMT

Can you post a sample of your file?

Re: Set Analysis on Aggregated Average

Anonymous — Sun, 10 Mar 2013 01:49:11 GMT

Apologies for the delay in responding. I don't seem to be able to attach files to the forum so here's a link to a cut down version of my file.

You will see that whenever a name is selected the vAvg and vDiff values are changed. I want the opposite, select the name and the vAvg and vDiff values remain as they are.

Re: Set Analysis on Aggregated Average

kaushiknsolanki — Mon, 11 Mar 2013 08:05:07 GMT

Hi,

Try this.

aggr (nodistinct (round (avg({1}race_winning_time),0.01)), race_course, race_type, race_distance_f, race_going, race_class, race_major)

aggr (nodistinct (avg ({1}race_winning_time)), race_course, race_type, race_distance_f, race_going, race_class, race_major) - horse_race_time

Regards,

Kaushik solanki

Re: Set Analysis on Aggregated Average

Anonymous — Mon, 11 Mar 2013 18:43:47 GMT

Thank you Kaushik, this works

I can now see where I was going wrong by putting the {1} at the beginning rather than within the aggregated average.

Thanks again.