how to get measure value for only null record in dimension

[prashantsanchet]

Hi all,

I have a customer as dimention with null values:

and i have this as measure. Now i want only this measure values where customer is null.

how we can write this in set analysis

num(Sum({$<GroupDate={'$(=date(yearend([GroupDate],-1), 'MM/DD/YYYY'))'}>}[sales]-[discount])

[vinieme12]

add below condition

Customer = {"=len(Customer)=0"}

[prashantsanchet]

Its not working i am trying in qliksense

[teempi]

Hi,

Try this: num(Sum({$<GroupDate={'$(=date(yearend([GroupDate],-1), 'MM/DD/YYYY'))'}> - <Customer={"*"}>}[sales]-[discount])

Edit: It appears you have a syntax problem in your original expression. Anyway, you need to subtract customers that are NOT null from your original state (that is done by " - <Customer={"*"}>").

-Teemu

[sunny_talwar]

I would suggest to create a null customer flag in the script

If(Len(Trim(Customer)) = 0, 1, 0) as CustomerNullFlag

and then use like this

Num(Sum({$<GroupDate={"$(=date(yearend([GroupDate],-1), 'MM/DD/YYYY'))"}, CustomerNullFlag = {1}>}[sales]-[discount])

Note: I also changed the single quotes around GroupDate's modifier to double quotes. Since you have single quotes around MM/DD/YYYY, it would be better if you use double quotes for the outer quoting.