Set Analysis on Aggregated Average

[Anonymous]

Hi

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

Can you post a sample of your file?

[Anonymous]

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.

[kaushiknsolanki · Accepted solution]

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

[Anonymous]

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.