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
    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?

  •     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

    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.

    for example

    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


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

Sign In Apply for Membership