Ranking categories

walsh · December 24, 2010 7:36PM

I've looked at the example .ioks that show how to determine the top 10 values by frequency. I'm trying to find the top x most frequently occurring categories in the attached and cannot for the life of me get RANK() to behave in a similar way for categories.

I have a data set that comprises several types of data, and the value ("Application") I want to rank is common to several of those types. I'm trying to get the rank order of the occurrence of each application where the type = "INC". When I first RANKed the name of the application, the rank order didn't go from 1,2,3... but that seemed OK ( it went, of course, from 1 to [count of most frequent]+1, but not easy to get the top 10 from that). So I aggregated the Application column and created a formula field that counted the occurrence of each category. I figured that RANK() would return a 1,2,3 value when applied to those numbers - but it simply returns the value I'm trying to order by rank. Can you put me out of my misery, and tell me what I'm doing wrong, please? I've attached the .iok I'm working on.

Many thanks, in advance,

KW

walsh · December 24, 2010 7:51PM

I probably shouldn't respond to my own question, but I can see why RANK() as I've coded it behaves as it does. It's presumably de-aggregating the data rather than working on the aggregated values - yes? But I still can't see how to get a 1-10 rank order from the frequency of occurrence of a value in a list of categories.

steve · December 26, 2010 4:10PM

Kevin,

I'm not sure I entirely understand your formulae, but changing "Ranker" to "(Formula result)" in the Aggregate drop-down does the trick.

I think AppCount should be "Singleton value", too, although "Non-empty count" also works. One trick for this kind of problem is to create a new field called "Record count" filled entirely with "1", perhaps using the formula "1". Then using Sum when aggregating.

Steve

walsh · December 27, 2010 3:58PM

Excellent! Thanks, Steve.

walsh · December 29, 2010 4:35PM

I'm afraid that I still have a question about this. What I'm trying to do is to select the 10 most frequently occurring categories within the data set. Now, in the table, and following Steve's recommendations (above), I've got the top 10 most-frequently listed app names with a value of "Yes" in the "Top 10 Ranking?" column. But the Yes/No selector doesn't do what I want - it selects only the first item (I think I see why - the counter showing the number of matches is the same as the number of instances of the most-frequently occurring instance and is > 10).

I've tried an additional formula field that selects whether the value of "Top 10 Ranking?" is "Yes" or not, but that gave the same result; looked OK in the table, but when I selected the "Yes"s, I got only one instance. I've tried using SUBSET() to pull out instances where the field is "Yes", but got nowhere.

What I'd like to do eventually is to create a filter that will select only 10 entries so that I can put them into a graph. Is it possible to do this in the way I'm trying to go about it?

Thanks,

KW

steve · December 30, 2010 11:37AM

By choosing "(formula result)" in the aggregate drop-down, the formulas are recalculated in the context of the aggregated subset data. This happens within the view. Whereas the yes/no filter shows the calculation within the unfiltered, unaggregated data, which doesn't work.

You should consider aggregating the data at source. If you have 2.6 DataManager, use the Aggregate block to aggregate the data in the same way you're currently doing in the view, then use the Field Organiser block to add the formula fields. Then load *this* data into Omniscope. If using 2.6 Professional, use the Aggregate operation in the Data menu, and Manage Fields to add your formulae.

This way, you will be able to filter the post-aggregation data.

If you want to retain both the unaggregated data and the aggregated data in the same file, see here:
http://forums.visokio.com/comments.php?DiscussionID=97&page=1#Item_1

Welcome!

Categories

Ideas Parade