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

[Jake_Raz]

I was creating a dashboard for someone, and one of the widgets was a column chart that plotted out the number of people per year of hire. So, x-axis is the year of hire from left-to-right, then each column is the number of people who were hired that year. So far so good.

I was asked if we could filter the chart so it only shows people who also had a different date occur within a year of them being hired. In other words: can we focus on just the new hires? Not the new hires right now, but people who were new hires at the time that this second date occurred.

Let's call the first date (that I'm plotting by) "Hire Date" and the second date (that I want to filter by) "Opened Date". As far as the data is structured, these are on different tables and can be a one-to-many situation (i.e. a given person only has one hire date but may be associated with multiple records and thus have multiple Opened Dates).

At first I thought this would be relatively simple. I added a new measure and used the following formula: DDiff([Days in Opened Date],[Days in Hire Date]). Then I thought "all I need to do is filter for the ones that are 365 or less".

This didn't work. Let's say a given year originally showed 10 people, and out of those, 5 were a "new hire" at the time they had an Opened Date (i.e. the other 5 people had Opened Dates that were after their year of hire). However, when I added my formula and then applied the filter, the amount would change to something much higher, like 47. When I change the view to something else like Table, it becomes clear that the filter is somehow duplicating rows when its present, but I don't understand why. If i deactivate/remove the filter then the duplicate rows go away, but then obviously I'm not getting the filtering that I need.

I also tried using a conditional formula. Something like: IF( DDiff([Days in Opened Date],[Days in Hire Date]) < 366, 1, 0). My hope was that, if this worked, then I could simply filter for the records that resolved to "1", and I could get proper counts by summing all the 1's. Unfortunately I keep on getting an inscrutable error message that says "Error in function definition (IF) in the 'condition': Expecting parameter of type 'Boolean' that consists of expression types 'Member Expression'. Instead found 'Dimension Expression'." This doesn't make sense to me, since why would "DDiff([Days in Opened Date],[Days in Hire Date]) < 366" NOT be a boolean? "DDiff([Days in Opened Date],[Days in Hire Date])" itself will always resolve to some sort of number, so we should always have something to compare to "366", and thus we should always be able to respond TRUE or FALSE, right?

In any case, does anyone have a good way of achieving the sort of filtering I need to do?

PLEASE NOTE: I do not have access to our eCube configuration, so any solutions that involve modifying the eCube or creating any new columns in the eCube are non-starters for me. Thanks!

[Angelina_QBeeQ · Accepted solution]

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

[Jake_Raz]

Hi! I tried using your MIN suggestion, but then it displays the following error: "Error in function definition (DDiff): Expecting parameter of type 'Dimension Expression' but found 'Numeric Expression'."

If I remove the MIN formulas, then it seems to work. However, it looks like the MIN formula is really necessary to get accurate counts. Without the MIN formula, certain people aren't being counted correctly when the more granular date columns are removed from the view (since now it combines the DDiff total for each row into a single total which is now higher than the cutoff).

[Jake_Raz]

Oh wait, I think I figured it out...instead of wrapping the individual elements in MIN, I wrapped the entire DDiff in MIN. This seemed to get it to calculate correctly!