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

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!

Welcome!

Categories

Ideas Parade

Tagged