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.
