Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
pindelicato
Contributor III
Contributor III

Count between two dates set analysis not working

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.

Labels (3)
1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

7 Replies
trdandamudi
Master II
Master II

Try one of the below and see if it works:

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

OR

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

 

pindelicato
Contributor III
Contributor III
Author

sadly neither work

chriscammers
Partner
Partner

 

 

You might want to try this

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

dplr-rn
Partner
Partner

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)

chriscammers
Partner
Partner

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

pindelicato
Contributor III
Contributor III
Author

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.

marcus_sommer
MVP & Luminary
MVP & Luminary

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