Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
BI-Controller
Contributor II
Contributor II

Set-analysis ignore filter (exclude values?)

Hello,


I have made a set-analys this that looks like below, which works fine:

Sum({$<[FactType]={'Actuals'}, [AccountNr]={"3*"} >}[Amount])

 

Now I want to add to the set-analysis a condition that if the filter "Scenario" is filtered, it should be ignored in this Measure. I.e. something like what I have below, where P* stands for the possible values in Scenario which could be "P202001", "P202002" and so one. But how could I make a set analysis that ignores what is selected in the "Scenario" filter?

Sum({$<[FactType]={'Actuals'}, [AccountNr]={"3*"}, [Scenario] not equal to {'P*'} >}[Amount])

 

Thanks

BI-Controller

Labels (1)
1 Solution

Accepted Solutions
Sergio3
Partner - Contributor III
Partner - Contributor III

U need to drop Scenario filter in this measure?

{<Fact_TYpe={'123'}, Scenario=>}

 

Or possible value this scenario?

 

View solution in original post

8 Replies
124rooski
Partner - Creator
Partner - Creator

can you try something like this -

Sum({$<[FactType]={'Actuals'}, [AccountNr]={"3*"}, [Scenario] -= {'P*'} >}[Amount])

not sure if you need to use single or double quote. If that doesn't work, you may also want to try !=.

BI-Controller
Contributor II
Contributor II
Author

Thanks for your reply! I tried with your suggestion and also changed to double quote like suggested. However still no output data, it looks like when I had this set analysis: [Scenario] -= {"P*"} it filters away everything, so I get zero output... 

 

Is there another way I could do the set-analysis, to instead say that all possible [Scenario] should be shown regardless of any filtering?

sunny_talwar

So, you want to ignore anything which is selected or do you want to ignore anything which begins with P within Scenario?

BI-Controller
Contributor II
Contributor II
Author

I want to ignore everything which is selected in [Scenario]. I just used "P*" because I did not now how to write ignore everything...

Sergio3
Partner - Contributor III
Partner - Contributor III

U need to drop Scenario filter in this measure?

{<Fact_TYpe={'123'}, Scenario=>}

 

Or possible value this scenario?

 

sunny_talwar

May be this

If(GetSelectedCount([Scenario]) = 0,
Sum({$<[FactType]={'Actuals'}, [AccountNr]={"3*"}>}[Amount]), Sum({$<[FactType]={'Actuals'}, [AccountNr]={"3*"}, [Scenario] = e([Scenario])>}[Amount])
)
BI-Controller
Contributor II
Contributor II
Author

Thank you Sergio3, this solution seems to work for me:

Sum({$<[FaktaTyp]={'Utfall'}, [Kontonr]={"3*"}, [Scenario]= >}[Belopp])

Again, many thanks for your help, all of you! 

Sergio3
Partner - Contributor III
Partner - Contributor III

Perfect!

Click on "Accept as soluton"

Than you!