Aggregating a result based on a median function

Tue, 20 Aug 2013 13:35:10 GMT

Hello,

I have a data set similar to below, but with far more data points:

FACNUM	SURVEYResult	Month	Quarter	Fiscal Year
000001234	77	Jan	Q1	FY13
000001234	72	Feb	Q1	FY13
000001234	73	Mar	Q1	FY13
000001234	74	Apr	Q2	FY13
000001234	75	May	Q2	FY13
000001234	76	Jun	Q2	FY13
000001345	65	Jan	Q1	FY13
000001345	69	Feb	Q1	FY13
000001345	68	Mar	Q1	FY13
000001345	70	Apr	Q2	FY13
000001345	76	May	Q2	FY13
000001345	67	Jun	Q2	FY13

FACNUM is a unique key representing a single facility, SURVEYResult is the raw data, and the rest should be self explanatory. Currently within my dashboard Month, Quarter, and Fiscal year are grouped into a cycle group called Year. What I would like to do is aggregate the results that come from the below logic:

Based on the position of the cycle group, take the median of the data set, then apply the following if-then logic:

If (Median(SURVEYResult)>= 77, 'Top Quartile', if(Median(SURVEYResult)<77 and Median(SURVEYResult)>=73, 'Abv Average', if(Median(SURVEYResult)<73, 'Below Average')))

The groupings are based on relative comparisons to an outside national benchmark. Ultimately, with this flag I want to count how many groups exist given the cycle group that is currently selected:

Count(Result of logic)

For example, if the cycle group was on fiscal year, then the result would return 1 facility that is Abv Average and 1 facility that is Below Average with scores of 74.5 and 68.5 respectively. If the cycle group ended on Quarter, then for Q1 and Q2 the results would be the same, but the raw values would be different:

Q1 - 1 facility Abv Average, 1 facility Below Average with scores of 73 and 68 respectively

Q2 - 1 facility Abv Average, 1 facility Below Average with scores of 68 and 70 respectively

Ultimately I want to approach it as dynamically as possible because this should work when an end user selects a custom grouping of months from a provided list. The Quarters and Fiscal Year grouping is being used as a default group when a user doesn't select any months.

I hope this made some degree of sense. I've tried employing the aggr() function but I think I might be goofing that up, as well. Any help is appreciated.

topic Aggregating a result based on a median function in QlikView

Aggregating a result based on a median function