Solved: Re: Set Analysis with substraction - Qlik Community

Applicable88 · ‎2021-02-12

Hello,

I have following set anaylsis which worked before:

count(distinct{$<Workplace={'5489'},Systemstatus={"*Open*"}>-<[InboundDate]={"*"}>}Orders)

I basically counting all the orders with these attributes, but exclude those which already have a inbound date. But I always get null values. When I take the last part with the exclusion out, i have values again. .

I chosed one of the "Exclusion methods":

eg: Field -= A

eg. {A} -{B}

and the third recommended mehtod which I also using: <A>-<B>

The mistake seems lie with the "<[InboundDate]={"*"} -part". Because when I take it out I have not null but values again.

But when i use an aggregation function in a straight table with this formula:

=aggr(count({<InboundDate={"*"}>}Orders),Orders)

I actually get a "1" count in all the order numbers which have a value for the inboundDate.

Have someone an idea whats wrong?

Thank you and have a nice Weekend.

Best.

marcus_sommer · ‎2021-02-15

I think you issue is that you want to select NULL - you formulated it differently but the exclusion of any values means you want to get the NULL's and this isn't possible within a pure set analysis respectively with a selection within a listbox.

That NULL's aren't stored in any way is often a benefit but if you need to select and even show them directly it's a disadvantage. Often the easiest method to bypass such challenge is simply to check this within the script and to set an appropriate default-value. This could be such things like 'NULL', '#NV', '' or also by numeric/date fields 0 or 01/01/1900 respectively 12/31/9999. In each case you could select those values and exclude unwanted values with appropriate set analysis.

Another way might be something like this:

count(distinct{$<Workplace={'5489'},Systemstatus={"*Open*"}>} Orders * (-isnull([InboundDate])))

- Marcus

View solution in original post

rubenmarin · ‎2021-02-14

Hi, it seems ok. I would test to select Workplace and systemstatus as it is on set analysis, and set a straight table with Orders as dimension ans Count(InboundDate) as expression, checking the option to show zero values, there is really any order that has a count of zero?

marcus_sommer · ‎2021-02-15

I think you issue is that you want to select NULL - you formulated it differently but the exclusion of any values means you want to get the NULL's and this isn't possible within a pure set analysis respectively with a selection within a listbox.

That NULL's aren't stored in any way is often a benefit but if you need to select and even show them directly it's a disadvantage. Often the easiest method to bypass such challenge is simply to check this within the script and to set an appropriate default-value. This could be such things like 'NULL', '#NV', '' or also by numeric/date fields 0 or 01/01/1900 respectively 12/31/9999. In each case you could select those values and exclude unwanted values with appropriate set analysis.

Another way might be something like this:

count(distinct{$<Workplace={'5489'},Systemstatus={"*Open*"}>} Orders * (-isnull([InboundDate])))

- Marcus

Applicable88 · ‎2021-02-17

Hello @rubenmarin ,

thank you very much so far. It isn't zero value. It should be Null. The ordernumbers and the inbound Date are in different tables. Its connected via Datamanager as a association link and not in some DataWareHouse via left join or etc.

So I think the Problem is that set analysis cannot "find" and exclude the ordernumbers where in the other table is still no inbound Date give as @marcus_sommer pointed out.

Thank you.