Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Set Analysis: One dimension equal to value, ignore selections in a second, respect all other selects

I've got an interesting case here: I have a fact table of revenues with a few relevant fields:

  • organization_id
  • initiative
  • income_account
  • is_forecast
  • recognized_date
  • amount_usd

My goal is to create a stacked bar chart wherein we show:

  • The sum of [amount_usd]
  • With primary dimension as [recognized_date] & second dimension [income_account]
  • Where [is_forecast] is FALSE
  • Ignoring any selections made in [initiative]
  • But allowing for selections in [recognized_date] and [income_account]

I've experimented with a number of constructions but I'm honestly at a loss here. I would have expected the following:

Sum({<[is_forecast]={FALSE}>,<initiative=>} amount_usd)

To work given my reading of various examples, but Qlik reports this as "Error in expression: '}' expected".

Any Set Analysis experts out there with suggestions?

Thanks!

1 Solution

Accepted Solutions
Employee
Employee

Try this:

Sum({<[is_forecast]={FALSE},initiative=>} amount_usd)

View solution in original post

3 Replies
Employee
Employee

Try this:

Sum({<[is_forecast]={FALSE},initiative=>} amount_usd)

View solution in original post

Highlighted
Contributor III
Contributor III

Excellent, thanks! 

I am still deeply unclear on when to segregate different clauses inside separate <> notations and how to concatenate those without throwing an error. (If there's no way to concatenate <> clauses, then I don't understand what those characters are there for)

Highlighted
Employee
Employee

A great tool is the Set Analysis wizard

http://tools.qlikblog.at/SetAnalysisWizard/

But basically the syntax is:

Sum({ $ <FieldName = {value(s)} > } FieldName)

and if you have multiple fields, then

Sum({ $ <FieldName1 = {value(s)}, FieldName2 = {value(s)> } FieldName)