Visokio website     Downloads     Video tutorials     KnowledgeBase  
Formulae: Aggregate rankings; Copying values from above - Visokio Forums
Formulae: Aggregate rankings; Copying values from above
  • DBuzacott December 4, 2013 8:02PM
    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(), "<=")))<br />

    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.
    Attachments
    Ranking screenshot.png 31K

Welcome!

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

Sign In Apply for Membership