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.
