Several Condition in Set Analysis

[Applicable88]

Hello,

both condition work on their own but not together:

Orders	EntryDate	DeliveryDate
12344	09.09.2020	15.10.2020
12345	10.09.2020	15.10.2020
12346	11.09.2020	21.10.2020
12347	12.09.2020	-
12348	13.09.2020	-
12349	14.09.2020	-

count({1<[EntryDate]={"<=$(=Date(today()-30))"},[DeliveryDate]={">$(=Date(0))">} distinct Order)

The first condition is "all Orders older than 30 days" and the sencond condition is "where there is NO delivery Date given" 

Since the product can be arrived, but maybe not yet dispatched. So I have null() values there. EntryDates are always present. 

Each work on their own, but not combined. Is the null the probleme? I also tried many different null() functions, and I need to count the Orders which is older than 30 Days but still in iventory. 

Hope someone knows a solution.

Best. 

[marcus_sommer]

There is a missing curly bracket by the second condition - therefore you may try:

count({1<[EntryDate]={"<=$(=today()-30)"},[DeliveryDate]={'*'}>} distinct Order)

- Marcus

[Applicable88]

Hi @marcus_sommer ,

I tried as you said. I also tried with the {*} instead of date(0), still getting Null in a KPI window. 

Best.

[marcus_sommer]

Do you use {*} or {'*'}. Further I noticed in your table the counting-field is Orders and in expression is Order - is it spelled correctly?

- Marcus

[Applicable88]

Hi, @marcus_sommer ,

I get a return now. The asterix apprarently needed double quote {"*"} to work. With single quotes I get no return. 

Maybe I didn't put my question well enough, but I acutally wanted the all counts, where I have a date in the first column but Null in deliveryDate.  Is there another way than using isNull()? In the sample table I showed it means, count where entrydate is given but no deliverydate given. 

Best. 

[marcus_sommer · Accepted solution]

NULL couldn't be directly queried within a set analysis but you could try to reverse the logic like:

count({1<[EntryDate]={"<=$(=today()-30)"},[DeliveryDate] -= {"*"}>} distinct Orders)

Not uncommon is also to set real values for these missing ones within the script. Depending on the requirements it may be 0 or today() or makedate(9999,12, 31) or maybe simply 'no date', for example with:

if(len(trim([DeliveryDate])), 'no date', [DeliveryDate]) as [DeliveryDate]

- Marcus

[Applicable88 · Accepted solution]

Hello @marcus_sommer,

thank you very much for your help.  The  reversing the logic "-=" isn't working as well.  I get no return. Set Expression is a very great tool.

But there are sometimes difficutlies when someone want to work with null values. Even if isNULL function and nullcount() function might count different in the "background", depending on Datatype, there is a big difference in the Syntax I just realized.

The is Null Syntax: IsNull(expr )

The Nullcount Syntax : NullCount({[SetExpression][DISTINCT] [TOTAL [<fld {,fld}>]]} expr)

With the Nullcount() I can do structures like this:

nullcount({1<[EntryDate]={"<$(=Date(today()-30))"}>}DeliveryDate)

The advantage for a use in a KPI Windows would be the possible use of identifiers. Which no If-statment provides. 

I conclude the best would be to follow your advise and to already "fix" problems in the load script or even DataWarehouse.

Thank you very much.