Set Analysis with substraction

[Applicable88]

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. 

[rubenmarin]

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 · Accepted solution]

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]

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.