I have a workbook where I have data for several radiologist clients.
Each client could have a number of different categories of the type of procedures they perform -- let's say about 10 or 12 each. (with thousands of records)
For example, Client1 could have Diagnostic, Ultrasound, MRI, etc. while Client2 could have Ultrasound, PET, Mammography, etc.
I want to present a parameter that allows the user to see a "full" view or a "collapsed" view of all the procedure types that are associate with a client. (They always view one client at a time--client is set as a context filter.)
I want the "full" view to show the regular list of ALL 10 or 12 procedures, sorted by descending volume.
The tricky part is that when the user selects the "collapse" option, I want to create groups around the different dimension values so that I show all the procedure types that have more than 2% of the total, and then lump all procedure types that have less than 2% each into a bucket of "Other."
So I might see just 6 major categories for Client1 and then the "Other" would hold the rest.
For Client2, I might have 10 categories, each of which has at least 2% of the overall volume--then just a couple (or none) in the Other...
I can get the 2% with this formula: sum([Number of Records]) / total(sum([Number of Records]))
I tried making an if statement as: IIF(sum([Number of Records])/total(sum([Number of Records]))< .02 , 'Other' , attr([ProcedureGroup]))
But then if I try to display the formula by itself, I just get the * instead of the breakout of each Procedure type...
I've slapped together an example and included it. I replaced Procedure Types with just letters, but hopefully you get the idea.
Sheet 1 shows what I got to work and sheet 2 shows the problem (when removing the 1st column) .
Ultimately, I want to use this on a bar chart across dates. (I want the calculations to be at the procedure level, I don't care about which dates had the volume)
procedure example.twbx.zip 40.6 KB