Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Function inside a Dimension

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

3 Replies
vgutkovsky
Master II
Master II

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

swuehl
MVP
MVP

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

Not applicable
Author

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