Tagged with aggregate-rankings - Visokio Forums

Ranking top x items - aggregated values

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

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

Formulae: RANK Syntax-Ranking on aggregated values, etc

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

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

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

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

Idea: Filtering-Pareto - custom ranges?

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

Formulae: Dense RANK Function?

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

Thanks for the help, Brian]]>

Idea: Value set segmentation: by percentiles?

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

Thank you, Ariel]]>

Ranking: Suppliers by total spend?

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

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]]>