Visokio website     Downloads     Video tutorials     KnowledgeBase  
Formulae: RANK Syntax-Ranking on aggregated values, etc - Visokio Forums
Formulae: RANK Syntax-Ranking on aggregated values, etc
  •     paola February 21, 2014 2:27PM
    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=
    # 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 
    # 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.  
    RankSubRank.iok 19K
  • 1 Comment
  • DBuzacott July 2, 2014 12:34AM
    Just wanted to say thanks for putting this together - was having a problem earlier today with this function and this has helped me figure out what was wrong (needed to change the aggregation function from 'sum' to 'formula result').


It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership