Direct Discovery in Action: How to substitute Set Analysis

    This series of community blogs is about the practical use of Direct Discovery, the ability of QlikView and Qlik Sense to directly query database tables using dynamically composed SQL statements ...


     

    How to subsitute Set Analysis


    There are not many QlikView applications without Set-Analysis in one or the other place. There are many reasons to use them

    • Set Analysis is needed whenever something should be calculated based on a different selection than the current user’s selection. E.g. Previous year comparison, count of all transactions, not just the selected …

    • Set Analysis is possible in any of the Aggregation Functions … Sum(), Count(), Min(), Max(), ... It is placed between angular brackets like {1}

     

    There is NO set analysis supported on DIRECT TABLES

    There is no TOTAL clause supported in DIRECT TABLES (e.g. SUM(TOTAL <AnyDim> FactField)


    Be aware of the following: If your aggregation expression contains a set modifyer, anything between the angular brackets will be cut off before the aggregation function gets parsed into the SQL statement fired against the direct data source. This means, you won’t get an error saying “Set modifiers are not supported”, the set modification simply gets ignored but the expression ifself will return results from SQL.


    But there are workarounds which allow you to cope with that. Let’s discuss two different scenarios of Set Modifications:


    1. A set that narrows the current user’s selections (another selection is added, but none is removed from the user’s selections
    2. A set that extends the current user’s selection (some or all selections get ignored and other selections may be set)



    1/3: How to narrow the user’s selections

    tt.png

    In this case, the set modifier can be substituted with an IF() function. Note that for in-memory data tables the recommended way is to use set modifiers even when the following syntax would also work for in-memory data.


    This has been discussed in this thread: Direct Discovery: Workarounds for Set Analysis


    2/3: How to get a different selection

    ggg.png

    In this case your friends are Alternate States, which are also supported for Direct Queries. The idea is the following:

    • The WHERE-clause of the automatically constructed SQL command is derived from the current selections
    • An expression, which – technically spoken – requires a different WHERE-clause than the user’s main selection, can be constructed in an Alternate State

    The actions needed for this are:

    • COPY STATES (from the Main State to the Alternate State)
    • One or more CLEAR FIELD or SELECT FIELD

    Link the object to that Alternate State. Now it calculates something different than the user’s main selection.

    • It will take you some considerations how to sync the alternate state when then user changes something in the main state (when the above set of actions should be repeated, either when clicking a button or silently with a trigger).

    Note: An object is linked only to one State at a time. It cannot query both states at the same time and calculate the difference (typically something you would like to do in comparative analyses). Read on, there are also workarounds to achieve this.


    3/3: How to make comparative analyses


    hhhh.pngThe last scenario is when the user has selected something and the app should make a comparison to some modified selection and calculate differences in the same chart.


    Here the so-called Interchart Functions are your friends! Get both, the user's current selection and the comparative selection into a state, then use interchart functions to get the two different results. If the user selected year 2014, additionally select 2013 as well. Then you reduce the selection as discussed under point 1/3, how to get just one year of the total set of 2013,2014.

     

    For details see here here Direct Discovery and Comparative Analysis (3/3)