Solved: simple set anlaysis with WHERE clause - Qlik Community

aaronnayan · ‎2017-07-04

Hi guys i need help with some set analysis

I want to =SUM(FUEL_MILES) where DRIVER is NULL

Fields:

FUEL_MILES

DRIVER

aaronnayan · ‎2017-07-04

Thankyou guys

For the help

Originaly it was Null in text

I made a change in the script so it would be easier in the future= IF(DRIVER>0,1,0) AS JO_DRIVER,

SUM( { < JO_HasDriver={0} > } FUEL_MILES)

View solution in original post

jmvilaplanap · ‎2017-07-04

Hi, Try with this

sum({<Driver={''}>}FUEL_MILES)

vishsaggi · ‎2017-07-04

Try this:

= SUM({< DRIVER = {"= Len(Trim(DRIVER)) = 0 "} >} FUEL_MILES)

Anonymous · ‎2017-07-04

try

sum({$-<DRIVER={"*"}>} [FUEL_MILES9])

Chernov · ‎2017-07-04

SUM( { < DRIVER -={'*'} > } FUEL_MILES)

-= : mean NOT

'*' : mean All values

aaronnayan · ‎2017-07-04

I want this set analysis to calculate FUEL_MILES when there is no driver. (NULL)

Anonymous · ‎2017-07-04

I think that won't work. You'll get the empty set.

Anonymous · ‎2017-07-04

is your "NULL" null() or 'null' ?

effinty2112 · ‎2017-07-04

Hi Aaron,

Maybe:

=sum(FUEL_MILES)-sum({$<Driver={"*"}>}FUEL_MILES)

Cheers

Andrew

Edit : Robin has written his expression in a far better way than mine. I think my formulation is easy to understand but once you do understand what's going on Robin's expression should be used.

vishsaggi · ‎2017-07-04

Did you try any of the expressions suggested? How is your null represented? Like actual word Null or '-' hyphen?