Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Trying to select null values in set analysis

I looked throughout the community and I can't seem to figure out why my expression isn't working for selecting values that are null.

The expression works fine without the one piece I added (which is the STORE_CONV_FROM-={'*'}, part).

I know that the dimension called STORE_CONV_FROM only contains numbers for certain stores who were previously open as a different store and I do NOT want to select them.

sum({$<LinkPeriod={0},
CalendarType={'Fiscal'},
WeekType={'Retail'},
Year={$(=max(Total Year))},
StoreStatus={'New'},
StoreRefOpenDate={'>=4/27/2014'},
%Date={'<=$(=date(CurrentDate))'},
STORE_CONV_FROM-={'*'},
CompleteWeekFlag
>}
NET_SALES)

Tags (2)
6 Replies
mov
Not applicable

Re: Trying to select null values in set analysis

See this: http://community.qlik.com/message/451785#451785

Rob's suggestion is reliable

Not applicable

Re: Trying to select null values in set analysis

Thanks Michael. I see that $-Field={'*'} works but it's applying it to everything.

I'm still confused how to only select the nulls for the STORE_CONV_FROM field while selecting everything else I want that isn't null.

mov
Not applicable

Re: Trying to select null values in set analysis

See if this helps (I'm using here exclusion operator - minus):

sum({$<LinkPeriod={0},
CalendarType={'Fiscal'},
WeekType={'Retail'},
Year={$(=max(Total Year))},
StoreStatus={'New'},
StoreRefOpenDate={'>=4/27/2014'},
%Date={'<=$(=date(CurrentDate))'},
  CompleteWeekFlag=>
-

  <STORE_CONV_FROM={'*'}>}
  NET_SALES)

Not applicable

Re: Trying to select null values in set analysis

Okay... so that formula supplies data now but it still includes stores that are NOT null for STORE_CONV_FROM. I added the STORE_CONV_FROM as a dimension to confirm this.

Also, for some reason, it's only showing the first week of data when it should be showing 42 weeks like it was.

mov
Not applicable

Re: Trying to select null values in set analysis

Well, I cannot add anything else without seeing your app.  If you can upload it, and if I have enough time, I'll take a look.

Not applicable

Re: Trying to select null values in set analysis

Thanks Michael but I can't do that. I'll just find a workaround or keep playing with it.

Thanks again for your help.