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

[peterkunhardt]

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!

[Lisa_P · Accepted solution]

Try this:

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

[peterkunhardt]

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]

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)