To the best of my knowledge, blending on calculated fields is possible. See the post below for details.
With that said, the issue you will most likely run into is that blending requires an equality constraint, whereas you really want to assign a top box score to a percentile rank if it falls within a specified range. As an alternative to this approach, I would recommend pre-aggregating your data in a custom SQL block, then joining it to the percentile rank table.
EDIT: As someone who works for a survey vendor, if the issue is that your percentile rank tables are split into multiple columns, I would recommend asking your survey vendor whether they could provide the file in a more convenient format. You could probably figure out a way to restructure all of those tables yourself (either manually or using macros), but if this is a recurring upload you don't want to have to do the restructuring each time you get more data.
Tara -- Just to understand this better -- Are you saying that you calc a value in your primary data source using a calc like the one you posted, and then you want to lookup [%ile Rank] from the secondary table based on the calc value from the primary source?
Stephen -- To the best of my knowledge, you can blend on a calc that is a dimension, but not a measure. And an aggregate calc will always be a measure.
Tara -- Stephen makes an excellent point about the format of the file. Have them add a column for question number, and put each percentile for each question on a separate row.
Ah. I looked at the link Stephen gave.
The solution there was a FIXED LOD. And a FIXED LOD can be a dimension, and is a dimension in the solution in the link.
Tara -- You very well might be able to make that aggregate calc into a FIXED LOD.
But you're still going to run into the issue that a successful blend requires data equivalence.
Thank you. It sounds like whether I achieve blending on that aggregate calc or not, a successful blend will still come down to the structure of the percentile rank look up table. However, they were very strict in terms of sending out "proprietary information" in an "non-editable format." (they only want to send these out as PDFs...!)
But as you and Joe Oppelt kindly stated, a data blend requires data equivalence. I will have to keep prodding my vendor for a usable file format, as creating macros to reshape these files every quarter is an unwanted addition to my workload!!
Glad to help! As for the percentiles, your best chance at getting the data in the format you want would probably be to submit a custom report request via your patient experience advisor. Hopefully that works out for you!