topic Set Analysis Function inside a Dimension in QlikView

Set Analysis Function inside a Dimension

Wed, 30 Nov 2011 17:33:59 GMT

Hello All,

I am trying to incorperate a cumulative frequency up to the data sets median within a chart dimension (The data set is below). The ideal outcome is that the function will return the total number people that waited less than the median for each month. The function I am using is

=count({<[Wait]={'<$(=Floor(Median([Wait],1))'}>} ID)

This works fine when a month and service has been selected but when there are no selections the '<$(=Floor(Median([Wait],1))' is equal to the waits for the whole of the dataset.

Is there any way to stop this and force the median calculated for each dimension.

Load * Inline[

Service, Month, ID, Wait
A Service, Jul_2011, 2048042, 1
A Service, Aug_2011, 1659531, 4
A Service, Aug_2011, 2096983, 5
A Service, April_2011, 677073, 7
A Service, Aug_2011, 2052211, 7
A Service, Aug_2011, 2143493, 7
A Service, Jul_2011, 2174455, 7
A Service, Jul_2011, 2048042, 1
B Service, Jul_2011, 2048042, 1
]

Thanks for your help

Peter

Set Analysis Function inside a Dimension

vgutkovsky — Wed, 30 Nov 2011 19:35:29 GMT

Peter,

What you are experiencing is an inherent restriction with set analysis. Set analysis is calculated once per chart, and does not understand what dimension it's currently in. You might want to use "total" with a "<>" (subtotal) qualifier, in combination with some conditional function(s), to achieve your results.

Regards,

Vlad

Set Analysis Function inside a Dimension

swuehl — Wed, 30 Nov 2011 23:42:47 GMT

Peter,

since your conditional function will probably involve the median function, an aggregation function, I think you will need advanced aggregation here, maybe like this:

=count( aggr( if([Wait]< Floor(Median(total<Month> [Wait]),1), ID), Month,ID,Service))

Regards,

Stefan

Set Analysis Function inside a Dimension

Fri, 09 Dec 2011 16:57:07 GMT

Thanks for the responses. Swuehl - I just tweeked it a little bit by including Service into the total <>

The final calculation that works out cumulative frequency to the median used for finding medians for grouped data within a service and month dimension is:-

=count(aggr( if([Wait]< Floor(Median(total<Month,[Service] > [Wait]),1), [ID]), Month,[Id],[Service]))

Regards

Peter