Forum Discussion

Jake_Raz's avatar
04-13-2023
Solved

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

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 ...
  • Angelina_QBeeQ's avatar
    04-14-2023

    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