Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Set analysis: exclude two or more of conditions

I have to exclude from all range of possible values combination of two in measure. Basically I need to sum all but year 2016 and month 5 and Total for field Amount should be 9.

I need solution with set analysis as in real data there are much more combinations and more conditions.

For example:

YearMonthAmount

2015

51
20165

2

2015

4

3
201675

at the moment tried something like this but it doesn't help

Sum({-<Month={5}>*<Year={2016}>} Amount)

Sum({$-<Month={5}>*<Year={2016}>} Amount)

Sum({$}-{<Month={5}>*<Year={2016}>} Amount)

Sum(-{<Month={5}>*<Year={2016}>} Amount)


So the question is really about syntax.

1 Solution

Accepted Solutions
MVP
MVP

Re: Set analysis: exclude two or more of conditions

Both of them seems to be working in Qlik Sense also

Capture.PNG

10 Replies
MVP
MVP

Re: Set analysis: exclude two or more of conditions

May be just this

=Sum({$-<Month = {5}, Year = {2016}>} Amount)

MVP
MVP

Re: Set analysis: exclude two or more of conditions

Or this

=Sum({-(<Month={5}>*<Year={2016}>)} Amount)

rubandoss786
Contributor

Re: Set analysis: exclude two or more of conditions

Hi Sunny,

    I tried the above logic with the sample Qlikview Data. But it is not working and i donot know why. Attached Excel and QLikview app for your reference. I am using Personalized edition and only in office we used licensed. So , i cannot open the changes that u make in attached Qlikview. Kindly share the expression in the comment itself. !Sorry to trouble u ..excluding two values.jpg@

padmaja_gade
New Contributor III

Re: Set analysis: exclude two or more of conditions

Try this:

Sum({(<Month-={5}, Year-={2016}>)} Amount)

rubandoss786
Contributor

Re: Set analysis: exclude two or more of conditions

No luck for me Padmaja, some how it is not working and i donot know the actual reason.

=sum({(<[Purchase month]-={2}>)}[Sales Value]) . not working !!!!

MVP
MVP

Re: Set analysis: exclude two or more of conditions

For the sample data provided, it seems to work well

Capture.PNG

Not applicable

Re: Set analysis: exclude two or more of conditions

Non of these helps. Probably you test it in Qlik View and I use Qlik Sense.

All responses tested - not working.

MVP
MVP

Re: Set analysis: exclude two or more of conditions

Both of them seems to be working in Qlik Sense also

Capture.PNG

Not applicable

Re: Set analysis: exclude two or more of conditions

Thank you! I will do further investigations. On simple data it works and I see that syntax is right. By some reason on my actual data it doesn't work.