Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
prashantsanchet
Not applicable

how to get measure value for only null record in dimension

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

4 Replies
vinieme12
Not applicable

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

add below condition

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

prashantsanchet
Not applicable

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

Its not working i am trying in qliksense

teempi
Not applicable

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

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
Not applicable

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

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.