topic Re: Trying to select null values in set analysis in QlikView

Trying to select null values in set analysis

Wed, 18 Feb 2015 17:05:13 GMT

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)

Re: Trying to select null values in set analysis

Anonymous — Wed, 18 Feb 2015 17:17:40 GMT

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

Rob's suggestion is reliable

Re: Trying to select null values in set analysis

Wed, 18 Feb 2015 19:28:10 GMT

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.

Re: Trying to select null values in set analysis

Anonymous — Wed, 18 Feb 2015 20:10:39 GMT

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)

Re: Trying to select null values in set analysis

Thu, 19 Feb 2015 14:41:02 GMT

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.

Re: Trying to select null values in set analysis

Anonymous — Thu, 19 Feb 2015 15:14:44 GMT

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.

Re: Trying to select null values in set analysis

Thu, 19 Feb 2015 15:18:13 GMT

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

Thanks again for your help.