Tagged with rank - Visokio Forums

Time Series Growth Calculation - Getting Incremental Numbers from Cumulative Total

MPatel — Thu, 12 May 2016 17:53:16 -0400

Hi,
I have data that's coming in cumulatively:

Date | # of Participants
4/1/2016 | 400
4/2/2016 | 500
4/3/2016 | 625

I want a field that will calculate the difference from the day before. So for 4/2/2016, it would show 100 (500-400). For 4/3/2016 it would show 125 (625-500). Please let me know what formula to use. Thanks!

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,

Missing Data: Automatically filling in the blanks

paola — Wed, 28 May 2014 13:58:50 -0400

When working with an incomplete dataset that contains blank cells, which should be populated based on the values of neighbouring cells above, the process of filling in the missing values can be automated in the DataManager to fully prepare the data for visualisation.

In the dataset below, missing values in the field [Ctr] should take their value from the previous day OR the last filled in date, for the same Campaign. A Sort operation block can be used for better transparency, to order [Date] and [Campaign].

Formula below will pick the last available value for each combination of Date/Campaign:

IF([Ctr]=null, 
SUBSET_LASTNONNULL([Ctr], SUBSET2([Campaign],[Rank Date],[Campaign],[Rank Date],"=","<")),
[Ctr])

It relies on the [Date] ranking:
RANK([Date],[Date],true,false, SUBSET([Campaign]))
For each campaign ranking runs from 1 to n, with the earliest date being ranked 1st.

In the case where their is no logical ranking criteria to sort inside the data set, the record number field may be useful (see below)

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?

Filtering: Dynamic rankings in a filtered view?

nash — Wed, 21 Jan 2015 03:15:25 -0500

I need to show the Top 3 brands depending on what is selected in the filters. For example there are 10 brands in the data, I select only seven and so I need the top 3 brands of seven selected.
At the same time I need to calculate what is the share of the every of these three brands of all the brands which are selected.
And the last requirement is to show the difference for every brand to the year before.

So far I work it out for all brand which are in the data with formulas in the fields.
However I would need more dynamic.

Is there a way to get it are you working on it?

Pivot View: Sorting and Ranking aggregated records/fields?

tagarw — Mon, 01 Sep 2014 07:22:52 -0400

I have one Pivot View having one field/column named [Selected Ratings].
Please see attachment for the problem and help me with this. Let me know if you need more information.

Filtering: Re-ranking filtered data sets?

abhishekdas1989 — Fri, 05 Sep 2014 06:16:58 -0400

Hi,

I have a medium sized data set ~2500 records. My filter is a checkbox filter (I can select any number of records, filter field is non-repeating text). The idea here is to make this filtered data a subset as I want to perform some operations on this filtered data.

The end goal is to assign a rank to each record of this randomly selected subset of data. So, if an additioal record is selected, the ranks may change based on the field value of the new record.

Formulae: Calculating the average of a 'top-N' subset?

tagarw — Mon, 01 Sep 2014 04:04:47 -0400

Hi,
I have a Table view in which data is changing according to the filters. For some selection suppose it has n number of records and 3 columns.

For one column I want to calculate average of top 20% values of it. How to write the formula for the same?

Formulae: Filtering top 10% of subset

abhishekdas1989 — Thu, 04 Sep 2014 04:14:52 -0400

Hi,

I intend to perform some basic calculations (mean) on a subset of the records in a table. I used Subset_Mean(field name, RowIndexSubset). However, it returns a null value. The RowIndexSubset is calculated properly (verified it using Subset_UniqueCount).

The main idea is to get the top 10% of the total records and then calculate the mean of the values in a specific field. This total records will change based on a filter selection (this filtering is working fine).

Ranking: Sorting based on variables?

tagarw — Fri, 08 Aug 2014 03:41:06 -0400

Can we customise the sorting operation in Omniscope. For example: I have a table with two columns/fields [empname] and [salary] and a Variable allowing the user to select between these two column names. For example, if user selects 'empname' as Variable value, data in table should be sorted by 'empname', and if user selects 'salary' as Variable value, then data in table should be sorted by salary.

See attachment for reference.

Thanks..

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: 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: Filtering-Pareto-'Top/Bottom N' sorting/display?

indranildatta — Mon, 16 Jan 2012 05:20:01 -0500

Hi,

I have been asked by quite a few of our clients and I think this feature would be a very powerful addition to the views in DataExplorer.

The simplest use case is as follows:

I have a Bar/Line view with measure X and split by one single field [Field1]. With this new feature, I'd be able to say, "Show only top 10 entries" and an automatic filter will be attached to that Bar/LIne view.

Building on the simplest case, we can think of further generalisations like:

1. Configurable number of entries, i.e. 'top/bottom 'N' entries', N to be chosen by user
2. Top/Bottom 'N' entries, rather than only top entries
3. Extension of the same concept over aggregated measures
4. Function support to enable the same feature through formulae

Please let me know if any of this is possible.

Cheers! Indranil

Idea: Add DenseRank function?

WKeith — Wed, 02 Nov 2011 04:35:29 -0400

In SQL you can use Rank which counts ties in a series, or Dense_Rank which does not - for example a sequence of 100,110,120,120,150 would give ranks of 1,2,3,3,5 but a dense rank of 1,2,3,3,4. This is useful when creating ranks when you are adding up rows of data before applying the ranking.

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

Bar Chart Order

jforero — Thu, 03 Mar 2011 15:36:40 -0500

Hello, we got the following bar chart that shows three years of information, we noted that Omniscope is ordering the bars based on the sum of the three years, but we wanted to order just using the first year, is it possible?

Table View: Displaying only top 'N' values

tagarw — Mon, 11 Aug 2014 02:55:05 -0400

Hi All,
I have one Table View in an Omniscope tab in which I am selecting certain rows from source DB say 100. Then I am creating certain filters on the Table View data which reduces the data to 50. And then a Sort.

After sort I want to display only top 5 records in final view of table. I am not able to find anything to do that. Can someone please help on limiting the records to 5.

Please let me know if you need more information.

Thanks.