Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
peterkunhardt
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
Lisa_P
Employee
Employee

Try this:

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

View solution in original post

3 Replies
Lisa_P
Employee
Employee

Try this:

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

peterkunhardt
Contributor III
Contributor III
Author

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)

Lisa_P
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)