topic Re: how to get measure value for only null record in dimension in App Development

how to get measure value for only null record in dimension

prashantsanchet — Tue, 23 Jan 2018 10:07:59 GMT

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])

Re: how to get measure value for only null record in dimension

vinieme12 — Tue, 23 Jan 2018 10:11:53 GMT

add below condition

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

Re: how to get measure value for only null record in dimension

prashantsanchet — Tue, 23 Jan 2018 10:36:51 GMT

Its not working i am trying in qliksense

Re: how to get measure value for only null record in dimension

teempi — Tue, 23 Jan 2018 12:15:06 GMT

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

Re: how to get measure value for only null record in dimension

sunny_talwar — Tue, 23 Jan 2018 12:18:13 GMT

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.