Tagged with aggregate-rankings - Visokio Forums

Ranking top x items - aggregated values

bernardpi — Mon, 18 Apr 2016 11:43:53 -0400

Hi there. I have a content view where I have written Javascript to run through a list of items and aggregate on a field in order to be able to rank the items. In context, I'm running through a list of employee transactions (not-unique) and ranking them by total amount spent. This involves performing a subset sum on all employee transactions and then performing a sort and reverse to get my sorted list which I then use for downstream calculations.

This is all done using for loops which aren't quick and results in the content view taking about 7000ms on a fast machine to load and on slow machines, Omniscope times out and the execution doesn't complete so the content view is blank.

I am not sure how to use the RANK function to do this (if indeed it can) as the docs are a little confusing and there aren't many decent examples. I would like to keep the calculation in the content view if possible.

Thanks for your help in advance,

Ranking: Wanting to Rank Dates Distinctly

aeastwood — Fri, 23 Jan 2015 12:35:45 -0500

I have a date field in one of my files, with about 50 distinct dates in it. I want a field ranking these dates from 1 to 50 (from earliest to latest) - I tried using 'RANK([Date], [Date], TRUE, FALSE, SUBSET())' in a new field, but that gives me 1, 145, 287 (because there are 144 records with the first date and 142 with the second date). How can I get it to rank them 'distinctly' for lack of a better way of putting it, like I described above?

Formulae: RANK Syntax-Ranking on aggregated values, etc

paola — Fri, 21 Feb 2014 14:27:18 -0500

Please see the demo file covering number of scenarios showing how to Rank all values or aggregated values; or rank on more than one field.
Some of the formulas used:
Rank function Syntax: RANK(value, field, isAscending, includeNulls, dataSubset)

Ranking Spend=
RANK([Spend])
# simple Rank comparing all values in the Spend field

Rank Spend per Week=
RANK([Spend], [Spend], false, false, SUBSET([Week]))
#Ranking Spend values inside each Week

SubRank Clicks =
RANK([Clicks], [Clicks], false, false,subset([Rank Spend per Week] ))
# secondary rank field, based on the Clicks value. Meaningful when multiple rows share the same [Rank spend per Week] value

Spend per Keyword=
SUBSET_SUM([Spend], SUBSET([Keyword]))
# total Spend for each Keyword

[One]=1
# basis for running total indexing

Running total per Keyword:
RUNNINGTOTAL([One], SUBSET([Keyword]))

Rank Totals per Keyword=
IF([Running total per Keyw]>1, null,
RANK([Spend per Keyword], [Spend per Keyword], false, true, SUBSET([Running total per Keyw],1,"=")))
# ignoring the values greater than 1, rank compares only the subset sum values where [Running total per Keyw] is 1, ensuring that only one value per category is compared.

Idea: Filtering- Pareto-'Top/Bottom N' filtering?

stuartjmccoll — Sun, 08 Jun 2014 18:13:45 -0400

Hi,

I'd appreciate if the record filter was changed so there is a setting to grab the top ten (or N) items for each category. At present this isn't possible and you have to do four steps to replicate this.

As an example, I'm working with Google Analytics data for multiple websites (using the API connector which is very handy) and I'd like to see the top 10 landing pages for each site. Not the most exciting request, but I imagine this has applications elsewhere. Thanks

Formulae: Aggregate rankings; Copying values from above

DBuzacott — Wed, 04 Dec 2013 20:02:28 -0500

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(), "<=")))

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.

Idea: Filtering-Pareto charts?

awillson — Mon, 23 Nov 2009 12:26:48 -0500

Is it possible to creat a Pareto 'type' view using two Bar/Line charts, one with the volume and one with the % count of each category?

Idea: Filtering-Pareto - custom ranges?

nevynb — Thu, 24 Feb 2011 01:59:43 -0500

Hi - I'm plotting various transactions recorded under a measure called 'amount' and when using amount in the pane by on the vertical axis AND the Pareto functionality the Pareto buckets are created correctly. My question is "can I specify the Pareto intervals, example 0 to 50000, then 50000.01 to 100000 etc? I know I can specify different numbers of intervals but the unfriendly aspect is that it typically starts an interval like 0 to 39347.77 etc which is not really applicable to my data. So whilst the mathematical Pareto works it is nonsensical when applied to my data.

Formulae: Dense RANK Function?

bgrovens — Thu, 24 Jan 2013 17:19:38 -0500

Hi Visokio - I have been trying to create a formula that will give me a simple Ranking. If I use the built-in function RANK and base this on application name the result set will be 1, 124, 300, 500, etc. What I need is a true application ranking 1,2,3,4. Oracle has a great built in function to do this, DENSE_RANK(). Any ideas on a work around or did I miss a function I could use that is already built into the tool? Here is what I have so far RANK([Application Name]).

Thanks for the help, Brian

Idea: Value set segmentation: by percentiles?

acohen — Fri, 13 Jul 2012 10:52:38 -0400

Is there a way to calculate percentiles in Omniscope 2.7? If not, is it possible to add this functionality in by generalising the Subset_Median function? This would be very helpful in terms of keeping some of our reports in an Omniscope format.
Thank you, Ariel

Ranking: Suppliers by total spend?

gilgorev — Mon, 27 Jun 2011 09:47:19 -0400

I'm trying to create a filter that will allow me to show only the top X (say 50) suppliers in an invoices database. Any ideas how?

A good example would be to look at the Visokio example on http://www.visokio.com/kb/subset-functions-2-5:
How do I add a column to it that will show the number 1 against John Swires and 2 against Anne Campbell, reflecting their ranking in terms of total spend?

Thanks!

Gil