Number per year where a different date is within a year of the first

Hi Jake_Raz,

Let's try to see the stuff in detail. I will use a test dataset and show a similar example. Hope this helps you with your formula.
I have an order with two dates: OrderDate and RequiredDate. I would like to see the number of orders that have more than 16 days difference between RequiredDate and OrderDate.
My table with detailed orders is here: 

My formulas:

// # of unique OrderID:
count([Order ID])

// DDiff:
Sum(DDiff(min([Days in RequiredDate]),Min([Days in OrderDate])))

// DDiff>16? :
case when Sum(DDiff(min([Days in RequiredDate]),Min([Days in OrderDate])))>16 then 1 else 0 end

An important case is the min function in ddiff. If I have more than one date per order, I should only take one. For example, you can have one HireDate and min will show you the same date and multiple OpenDates. There is still only one person, but which OpenDate should we use? So you can play with aggregation for Dates per Person.

The following table is similar to the previous one. Here we will understand, how to work with multi-pass aggregation (or grouping).

In the final widget, we will not have an OrderID, but this is important for the calculation. If we drop it, the case formula will show us 1. Because it will calculate the difference according to the current order date and the minimum required date for that order date. We must leave in the table the amount of this 1 for each order.

And in third table we will have the next formula:

// # of Orders with DDiff>16:
sum([OrderID], case when Sum(DDiff(min([Days in RequiredDate]),Min([Days in OrderDate])))>16 then 1 else 0 end)

We used OrderId as a grouping, set a case statement for each order, and summarized the results of those cases.
In your opinion, it will be any person ID instead of order ID.

Always here to help,
Angelina from QBeeQ
[email protected]
QBeeQ  - Gold Implementation and Development Partner