Direct Discovery: Workarounds for Set Analysis (1/3)

    Note:

    • This community entry is part of a multi-chapter blog: Start here: Direct Discovery in Action: How to substitute Set Analysis
    • This workaround is to make a calculation based on a more narrow selection than the current selection, for example you like to get Sales for Germany only. The Set Analysis would be: Sum({<Territory.Name={'Germany'}>} LineTotal)

     

    Any string after an Aggregation formula Sum(), Count(), Min(), Max() where you typically would put a set modifier, is simply ignored when the SQL command is computed. So if LineTotal is a Direct measure field and you use Sum({1} LineTotal) the SQL will only contain Sum(LineTotal).

     

    Note: Instead of the If( ... = ...) equal match you may also use LIKE operator or functions as "Match()" or "WildMatch()".

     

    TestFormulaResultWhy?
    1Sum(If(Territory.Name = 'Germany', LineTotal))FailesThe underlying SQL database does not understand the inline-IF syntax of QlikView
    2Sum(CASE WHEN Territory.Name = 'Germany' THEN LineTotal ELSE 0 END)FailesEven when this is correct T-SQL syntax, QlikView doesn't allow native SQL and wouldn't send this
    3If(Territory.Name='Germany', Sum(LineTotal))Works if ........ works if the Dimensionality is Territory.Name. Does not work for totals and not if the dimensionality is different
    4Aggr(
    If(Territory.Name='Germany', Sum(LineTotal))
    ,Territory.Name)
    WorksThe Aggr around the If(..Sum()) construct ensures that the right dimensionality is given. This works for the total rows and the detail rows, when the dimensionality is Territory.Name.
    5Aggr(
    If(Territory.Name='Germany', Sum(LineTotal))
    ,OtherDimension, Territory.Name)
    WorksIf the dimensionalty is something different than Territory.Name but yet the subset of a specific Territory.Name should be calculated, you need both dimensions in the Aggr. This means, the expression has one deeper dimensionality than the chart itself. However, a grand total row does not work, as it has no dimensionality. See next test ...
    6Sum
    Aggr(
    If(Territory.Name='Germany', Sum(LineTotal))
    ,Territory.Name)
    )
    WorksWrap another Sum around the Aggr then the same formula works in the detail rows (like in test 5) but also in the total rows.

     

    Complex? Yes. I agree. To build such a formula step by step

    >>> watch my attached Flash video