Just wanted to share two methods I've got for seemingly common data problems. I don't claim that these are perfect (or even particularly efficient), but they do work. So I've detailed them here so that no-one else has to reinvent the wheel.
Power Tip 1) Aggregate rankings I've got a file with the basic structure as follows: [Name] [Value] and a whole lot of other fields
Aggregated rankings can be calculated as per this discussion (http://forums.visokio.com/discussion/comment/7339/). However, I wanted something that would display these rankings on non-aggregated data, as well as driving formulas for 'Top 10' and 'Top 100' categories (in this example, one of the KPIs is spend with top 100 Names).
To do this, I implemented the following workflow Split off data Delete excess fields (i.e. all but name / value) Aggregate by Name Rank by value (do not preserve formulas in output) Merge rankings back into the original data A screenshot of this is attached
Power Tip 2) Copying values from above We have a dataset with the format [Site] A null null B null null
i.e. a field that is only populated for the first row of each section of data. There is some discussion of how to copy these values down into the null cells here (http://forums.visokio.com/discussion/comment/8577/#Comment_8577) but I'm not familiar with these scripts and so didn't want to use them.
Instead, I created some new formula fields as follows: [Linenumber] = currentrow() [Linenumber Site] = if([Site]=null, null, currentrow()) [Site updated] = CELL([Site], SUBSET_MAX([Linenumber Site], SUBSET([Linenumber], CURRENTROW(), "<=")))<br />
This creates a new field [Site updated] with the values copied down. With the current dataset (19 fields, 10k records) these forumlas run pretty much instantly. If you wanted to get sneaky, you could then rename [Site updated] to [Site] and delete the added fields. This would eliminate the calculations from the final file.