Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

How to ignore all selections in multiple condition sum expression

I have tried to look up similar questions but can't seem to get my exact expression to work. I have a KPI using the formula:

Sum({1<[Company Name] -= {''}>} [Sales]) i.e sum of sales where Company Name is not null.

 I want to ignore all selections and have tried the following options with no success, please help:

=Sum({1<[Company Name] -= {''}>} [Sales])

=Sum({1<[Company Name] = >}If(Not IsNull([Company Name]),[Sales]))

=Sum({1<[Company Name] = >}{<[Company Name] -= {''}>} [Sales])

=Sum({<[Company Name]-={''}>} {<[Company Name]=>}[Sales])

Sum({1<[Company Name]-={''}>} {<[Company Name]=>}[Sales])

Labels (1)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Hi @H_M ,

I am happy that we got the solution, however, I am also afraid that the solution might not really behave the exact way you might need.  Your expression should be something like:

Sum({1<[Company Name] = {"*"},FIeld1 = $::Field1>} [Sales]) 

instead.

I wrote an article on it, you might want to read to understand why. Check: Ignore-all-selections-except-few-fields-using-Set-Analysis

View solution in original post

6 Replies
Highlighted
MVP
MVP

Id your base expression working where you want to exclude nulls? I guess, if you have real nulls in that field, it should NOT work. Give a try with :

Sum({1<[Company Name] = {"*"}>} [Sales])

 

Highlighted
Community Manager
Community Manager

@H_M Hi, What product are you using, QlikView or Qlik Sense? I would like to move this into the correct product forum.

Sue Macaluso
Highlighted
Contributor II
Contributor II

Hi @Sue_Macaluso , I am using Qlik Sense

Highlighted
Contributor II
Contributor II

@tresesco For some reason, that expression is not yielding the correct figure. Perhaps the issue is with my source data, I will re-look at it

Highlighted
Contributor II
Contributor II

@tresesco turns out it was giving me the wrong answer simply because it was ignoring all selections, what I needed was for it to ignore all selections except for one field (let's call it 'Field1') so this formula is what worked:
Sum({1<[Company Name] = {"*"},FIeld1 = p(Field1)>} [Sales])

Highlighted
MVP
MVP

Hi @H_M ,

I am happy that we got the solution, however, I am also afraid that the solution might not really behave the exact way you might need.  Your expression should be something like:

Sum({1<[Company Name] = {"*"},FIeld1 = $::Field1>} [Sales]) 

instead.

I wrote an article on it, you might want to read to understand why. Check: Ignore-all-selections-except-few-fields-using-Set-Analysis

View solution in original post