Visokio website     Downloads     Video tutorials     KnowledgeBase  
Menus: formula and aggregation/grouping interaction (2.7+) - Visokio Forums
Menus: formula and aggregation/grouping interaction (2.7+)
  •     steve November 9, 2011 6:12AM
    This idea attempts to address the potential to easily misconfigure Omniscope to show the wrong data through unsuitable combinations of sum/mean/formula and aggregation/grouping.

    These ideas would not affect pre-configured IOK files; they would only change default settings, and hide inactive unusual choices.

    1. In the function picker, only show functions which have the same resulting data type (in the same "logical value domain") as the field they are applied to.

      Visible choices (depending on field data type):
      • Sum
      • Mean
      • Median
      • Mode
      • Minimum
      • Maximum
      • Singleton value
      • First value
      • Last value
      • (Formula result)


      Power-user hidden choices:
      • Record count
      • Empty count
      • Non-empty count
      • Unique value count
      • Unique values list
      • Range
      • Standard deviation


      You would still be able to get at the power-user hidden choices easily but would not see these by default.

      This is necessary for (3), to avoid aggregation/grouping from changing the data structure and preventing formulas from working. Formulas are defined to operate over underlying raw data, and if your aggregation changes a text field into a numeric through the use of "Empty count", the formula will most likely fail.

      This change would occur in grouping and aggregation, but perhaps also elsewhere for consistency and to remove choices which are confusing - e.g. a value labelled "Sales volume" but showing "Range" is incorrect.

    2. In the Table view, we provide a "Formula result" option for each field alongside sum/mean/etc., just like with aggregation, instead of the "calculate formulas over grouped results" precedence setting.

    3. In aggregation and grouping, we make "(Formula result)" the default for any formula field. This would include the Table view grouping, in-view aggregation, and the Aggregation operation in the Data menu and in DataManager.

      Note: While the bar view aggregates automatically, we couldn't allow choosing "Formula result", since Omniscope couldn't tell whether to sum/mean/whatever in any fields referenced by formulas. Instead, you would use the new "Formula" measure in 2.7, which allows you to write your own field-independent formula where you are required to use SUBSET_SUM/MEAN as appropriate.



    This would mean that, if you have formulas configured in the underlying data, and you are rolling up a view to show the total sales per region, your formulas would re-execute by default.

    However, due to the power and complexity of formulas, we would like to get feedback from our power users as to whether this would be a net-positive change. Would it reduce the incidence of incorrect configuration, for common models?
  • 4 Comments
  • Guy_Cuthbert        Guy_Cuthbert November 9, 2011 6:39AM
    Steve

    Looks good - I need the have a think about all the scenarios we have experienced in the pasts, and consider how your proposals would affect them... but my initial reaction is that this would help avoid silly mistakes, and therefore is a very positive move.
    Atheon Analytics Ltd
    w: www.atheonanalytics.com
    e: guy.cuthbert@atheon.co.uk
    t: +44 8444 145501
    m: +44 7973 550528
    s: guycuthbert
  •     bgrovens November 30, 2011 4:14PM
    Steve,

    I have been working with 2.7 and saw this comment thread. I absolutely need the SUM function added back into the available measure list as you can see attached below. This function was crucial in 2.6 for several reports we have built. Say I have a column of items entitled duration minutes. The numerical values need to be added up over a monthly period to show in a graph. The simple 2.6 sum measure did this perfectly for us.

    Thanks,
    Brian
    Attachments
    NoSum.JPG 26K
  •     steve December 1, 2011 1:54AM
    Brian, none of the above idea has been implemented, and even if it had, we would not intentionally remove the 'sum' option for a numeric field.

    It looks like the field you've named "Number of records" has turned into Text somewhere. This would explain the set of functions you have available. Check your data source.

    If you do find that Sum has disappeared for numeric fields, this would be a bug, and we would need an IOK file illustrating the problem.
  •     steve December 5, 2011 6:29AM
    (1) has now been implemented. See here: http://forums.visokio.com/discussion/1139/function-filtering/p1
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