topic Re: Count between two dates set analysis not working in App Development

Count between two dates set analysis not working

pindelicato — Wed, 22 Dec 2021 15:42:54 GMT

I have three dates Ship Date, Order Date and Due Date and I am doing set analysis to count the # of orders that have a ship date between the Order and Due Date like so;

count(Distinct {<[Shipdate]={">='$(=[OrderDate])'<='$(=[DueDate])'"}>} Order_Number)

I've also tried:
count(Distinct {<[Shipdate]={">=$(=[OrderDate])<=$(=[DueDate])"}>} Order_Number)

and even made Num variables for the dates to make the comparison 'easier'

count(Distinct {<vNumShipdate={">=$(=vNumOrderDate)<=$(=vNumDueDate)"}>} Order_Number)

And I am continually getting bad returns on it, usually giving me a positive when my If statement check shows it shouldn't be.

I am checking this set analysis against:

If ( [Shipdate] >= [OrderDate] and [Shipdate] <= [DueDate], 1,0) ,

An example of my data looks like this

Order # || Shipdate || Orderdate || DueDate || Set Analysis Count Column || If Column

1 || 9/1/2018 || 8/28/2018 || 9/5/2018 || 1 || 1

2 || 9/3/2018 || 8/20/2018 || 8/30/2018 || 1 || 0

Hopefully that reads clear, the second example is what I am seeing where the count with set analysis is saying that 9/3/2018 is between 8/20 and 8/30 and I am not sure what else to try.

Re: Count between two dates set analysis not working

trdandamudi — Fri, 30 Aug 2019 18:04:55 GMT

Try one of the below and see if it works:

count(Distinct {<[Shipdate]={">=[OrderDate]<=[DueDate]"}>} Order_Number)

count(Distinct {<[Shipdate]={">='[OrderDate]'<='[DueDate]'"}>} Order_Number)

Re: Count between two dates set analysis not working

pindelicato — Fri, 30 Aug 2019 18:15:14 GMT

sadly neither work

Re: Count between two dates set analysis not working

chriscammers — Fri, 30 Aug 2019 19:22:33 GMT

You might want to try this

Count({<Order_Number = {"([Shipdate]>=[OrderDate] AND [Shipdate]<=[DueDate])"}>}Distinct Order_Number)

Re: Count between two dates set analysis not working

dplr-rn — Fri, 30 Aug 2019 19:36:47 GMT

I would try chris 's method if you want to do it on the front end.

But why not just do this on the script? Create a flag for this condition and do a simple set analysis for that flag

e.g.

If ( [Shipdate] >= [OrderDate] and [Shipdate] <= [DueDate], 1,0) as ShippedOntimeFlag

expression can be

count(Distinct {<[ShippedOntimeFlag]={'1'}>} Order_Number)

Re: Count between two dates set analysis not working

chriscammers — Fri, 30 Aug 2019 19:41:22 GMT

Lol, I was answering the question as asked, but I would prefer a solution that is calculated in the load script.

Re: Count between two dates set analysis not working

pindelicato — Fri, 30 Aug 2019 19:48:17 GMT

Unfortunately none of the front end solutions seem to be working, so yeah we were leaning to do it in the load script now. Was really just trying to avoid that, because at that point I could just do it in the view we are pulling from.

I do appreciate all the input. It is really weird that the set analysis doesn't work, it appears written correctly.

Re: Count between two dates set analysis not working

marcus_sommer — Mon, 02 Sep 2019 12:49:25 GMT

It couldn't work in the way you tried it. In your first trials you used (adhoc) variables which have only a single value in the context of an object because the variables are evaluated at first and their results are then applied to each occurance within the object.

Your other trials with the "classical" set analysis won't work because a set analysis condition is like a selection in a field - means it checked only if the specified/selected values are possible or not. And this is completely independent to the (possible) values in another fields. This means a set analysis is not designed to make row-based comparisons.

What you might trying is to implement an explicit if-loop or an implicit one, like:

Count({<Order_Number = {"=[Shipdate]>=[OrderDate]<=[DueDate]"}>}Distinct Order_Number)

It will enforce a row-level comparison but it won't not be better than using a classical if-loop.

But much better as this is the already suggested script-solution. It's much easier, more performant and provides far more possibilities as you could reach within the UI, for example not just using simple 1/0 flags else substracting the dates and/or assigning buckets to them and so on ... probably this would be the next questions from the users if they get a count and maybe any percentage of them ...

- Marcus