Visokio website     Downloads     Video tutorials     KnowledgeBase  
Formulae: Calculating different queries in a Formula - Visokio Forums
Formulae: Calculating different queries in a Formula
  •     Panos May 7, 2014 4:56AM
    Hi
    I use Content View and I have created 2 different tables in the same tab, which change dynamically according to the filters and all the data in the tables retrieved by formulas.

    On Table 2 I have kept the data isolated from specific filters, but in some rows I would like to do some additions and subtractions by using the data from Table 1, but when I write down the same formula from Table 1 to Table 2 I get different results, which makes sense because Table 2 uses the whole data.

    Question: Is there any formula which is able to maintain a specific query or filtered data within?

    Thanks
    Panos
  • 6 Comments
  •     paola May 7, 2014 5:25AM
    Each view in Omniscope can be set to display 'All data', filtered data, query, intersection between filtered/query, selected data (responding to selection in another view) and more.
    You may wish to use a SUBSET formula, where you can specify multiple criteria to create subset on which you wish to perform calculation.
    Please see more on Subset functions http://www.visokio.com/kb/subset-functions

    If this doesn't answer your question, please post a sample file.
  •     Panos May 7, 2014 6:15AM
    Yes, I have found subset formulas really useful, the thing is that data stops to be a kind of dynamic with subset formulas. I mean when I have set in the formula April as the current month, that is not going to change in May.
    It is quite tricky for budget calculations.
  •     paola May 7, 2014 6:17AM
    You said your formula was in the Content view - result should respond to filtering.
    Please post a sample either on forum or directly to support (at) visokio (dot) com.
  •     tjbate May 7, 2014 6:18AM
    Panos: You can use Variables inside Subset functions, such that users can select start and end-points for calculations using sliders. You can also use evaluated expressions like TODAY.
  •     paola May 7, 2014 11:19AM
    Currently it is not possible to make direct references from one cell to formula result in another cell or table, but you could use Declare function instead.
    http://www.visokio.com/functions-guide/declare

    for example

    Declare(
    CurrentMonth,DATEUNIT(TODAY(), "month"),
    SUBSET_SUM([Value], subset([Month],CurrentMonth-1,"="))
    )

    You can use dynamic formulas or variables, that will adjust to change in date/month.
  •     Panos May 7, 2014 11:44AM
    Thank you Paola, it seems useful!
This discussion has been closed.
← All Discussions

Welcome!

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

Sign In Apply for Membership