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


    • 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()".


    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
    If(Territory.Name='Germany', Sum(LineTotal))
    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.
    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 ...
    If(Territory.Name='Germany', Sum(LineTotal))
    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