Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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])
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
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])
@H_M Hi, What product are you using, QlikView or Qlik Sense? I would like to move this into the correct product forum.
Hi @Sue_Macaluso , I am using Qlik Sense
@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
@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])
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