I have a requirement to display a current percentile ranking, and then the next higher percentile range. For example
If the current ranking is in the 50th percentile and the rate is 12.1, the user wants the rate for the 75th percentile say its 14. To grab the percentile rankings, I used this query (and it works):
2010Percentile: IIf([ReportingYear]=2010,IIf([Rate]>=[201010th] And [Rate]<[201025th],"10th",IIf([Rate]>=[201025th] And [Rate]<[201050th],"25th",IIf([Rate]>=[201050th] And [Rate]<[201075th],"50th",IIf([Rate]>=[201075th] And [Rate]<[201090th],"75th",IIf([Rate]>=[201090th],"90th"))))))
However, the next step is to display the actual rate. When you have a rate in the 90th percentile, the user wants "N/A" display as there is not a higher rate. However, I keep getting an #Error only for the 90th percentile rate when I run the below query. The rest of the rates display perfectly:
2010NextPercentileRateTESTTEST: IIf([ReportingYear]=2010,IIf([Rate]>=[201010th] And [Rate]<[201025th],[201025th],IIf([Rate]>=[201025th] And [Rate]<[201050th],[201050th],IIf([Rate]>=[201050th] And [Rate]<[201075th],[201075th],IIf([Rate]>=[201075th] And [Rate]<[201090th],[201090th],IIf([Rate]>=[201090th],"N/A"))))))
I know there is probably a smoother way of doing this, however, I have never had to build such a complex report.
Any suggestions on why I get the #Error displayed where ever there is a 90th percentile rate?
Tidak ada komentar:
Posting Komentar