Qlik Community

QlikView Documents

Documents for QlikView related information.

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

Employee
Employee

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

Tags (2)
Labels (2)
Attachments
Comments
Not applicable

Great job, Christof ! This is really good to know.  Do you think there ever will be a features do things like ignore a field selection (Year=)?  We use this a lot in many of our applications

Employee
Employee

I forwarded this idea to R&D. What you can do at least, is working with Alternate States. If you want one chart to ignore, say, the selection in Year and show all years, give the user an action (button or trigger), that copies anything relevant of the selection to an alternate state but then reset the year. The chart then should be linked to that state instead of the default state. The chart will then follow a different selection.

Not applicable

Hi, Great info. I understand that the work around shown is for the dimensional field (Territory.Name='Germany'). Is possible to achieve the same for an Metric level field? And is it possible to have a expression itself inside the IF condition.

Ex: (Metric_Field = (count( DISTINCT <Field_name>)=1 ))...

From your above example Im trying to write....

........

Count

Aggr(

If( (Metric_Field = (count( DISTINCT <Field_name>)=1 )) , Count(ID's))

,Metric_Field)

)

Not applicable

Hi Shawn, Did you get an answer to this one? I have a similar use case.

Thanks

Version history
Revision #:
1 of 1
Last update:
‎10-20-2014 11:10 AM
Updated by:
Employee