- This community entry is part of a multi-chapter blog: Start here: Direct Discovery in Action: How to substitute Set Analysis
I managed to make a comprehensive, generic approach for a COMPARATIVE ANALYSIS WITH DIRECT DISCCOVERY
Since Direct Discovery translates queries into SQL, there is no Set Analysis possible. Therefore, we need to approach it in a different way.
► There is a 5" Flash video attached below, in which I am introducing the sample app.
Those are the "ingredients" of this solution:
- During selecting the two sets, I use two different alternate states. Currently, one field from the set is considered for making comparision (can be extended if needed), e.g. YearMonth.
- Once you are done selecting the two sets, the main selection in your dimension must then include the selections from both sets (What's not in the WHERE, won’t be there ...).
- There is no set analysis, but I solved it with intra-chart-functions and MATCH() clauses in the "QlikView-side" of the chart
- I used a Pivot table and put the comparision-dimension (YearMonth) as a column-dimension (it has to be there); the row-dimenion can be choosen by you.
- The "Total columns" calculate the aggregated comparison in absolute and relative changes (the “Total aggregation” in fact is a RangeSum(First(…)…) combination = Intra-chart-function)
- The detail columns show, how the details for the different sets. There are two versions of the chart, one without the details (the results is in the total-part) and one with details. Be aware: both versions require the comparision-dimension as the column-dimension even if the "without detail" chart seems not to use it.
- The SQL Statement uses a “normal” statement and has “no idea what happen on the QV side” … that will keep it performant (No Aggr() etc.)
► Version "v1-13.qvw" is using AdvetureWorks2012 database on an MS SQL Server.