Visokio website     Downloads     Video tutorials     KnowledgeBase  
Graph View: Dynamic ranking/filtering of time series? - Visokio Forums
Graph View: Dynamic ranking/filtering of time series?
  • jforero     jforero January 18, 2011 6:34PM
    I was wondering if It is possible to rank a series using the Graph view?

    In other words, if i have a list of 25 series, I wanted to be available to choice only the first 10 series, for example, using the stat average? Is it currently this possible?
  • 4 Comments
  • jforero     jforero February 3, 2011 2:05PM
    Please find an example chart, we wanted to be available to select AUTOMATIC (without need to use the filters) only the top (10,5, etc.) lines to show in this view.
    In this specific examples the criteria to rank, It should be the average of this lines over the time, or, MAX value, etc...
    Attachments
    GraphViewchart.jpg 835K
  •     steve February 7, 2011 11:11AM
    The RANK function currently only allows you to rank individual records by their value within a column (optionally a subset of the data). Whereas *you* need to apply the RANK after aggregating the data, so you are ranking each time series (a set of multiple records) against other time series.

    There is a good workaround, however. See attached IOK file.

    Here I have created a sample set of 4 time series identified by field "Cat" (A, B, C, D).
    "Val" is my Y axis, and "Time" is my X axis. Each time series has 4 points. I hope this corresponds accurately to your data structure.

    I have a field "One" containing 1 in every cell, purely so I can use the new RUNNINGTOTAL function to determine the index of each record within its category ("Per-cat index").

    I separately find the max value in each time series ("Per-cat max").

    Finally, I use RANK to find the position of the time series against all other time series. I pass in SUBSET([Per-cat index], 1) so each time series only counts as one record for ranking effectiveness.

    In this file, I'm ranking to show the top 3 out of 4. You should be able to adapt all this for your file perfectly.

    Ideally, in future, we would have additional functions that would facilitate this kind of problem. Ideas welcome - please post here. For example, instead of using the "One" field and a RUNNINGTOTAL field, we could add an optional "subset" argument to CURRENTROW, yielding the record number within a subset.
  •     steve February 7, 2011 11:12AM
    Apologies - there appears to be a problem with attachments in this page. I'll post the file later.

    Meanwhile, here are the formulae:
    "Per-cat index": RUNNINGTOTAL([One], SUBSET([Cat]))
    "Per-cat max": SUBSET_MAX([Val], SUBSET([Cat]))
    "Rank": RANK([Per-cat max], [Per-cat max], SUBSET([Per-cat index], 1))
  • admin February 7, 2011 2:06PM
    Here is the file Steve is referring to.
    Attachments
    Rank time series.iok 7K

Welcome!

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

Sign In Apply for Membership