Data360 Analyze

 View Only

  • 1.  Calculate the Number of Days Between Two Dates

    Employee
    Posted 03-11-2020 04:36

    Hi,

    I try to get information when customers is made first and last order.

    I have field orderdate in date format.

    I run data thought Aggregate node, using operations first and last.

    Output is "orderdate_first" and "orderdate_last".

    Then I calculate diffrence between these two dates.

    DiffDates = (orderdate_Last - orderdate_First).days

    But some values in Diffdates files are negative or posivite digits and that don't make sense.

    Root cause must be in Aggregate node. 

    Can you tell how I get right way first and last date information for data.

     

    CUSTOMER_ID DiffDates orderdate_First orderdate_Last
    1 -287 4.4.2019 21.6.2018
    2 126 25.5.2018 28.9.2018
    3 -10 29.3.2018 19.3.2018
    4 0 9.1.2019 9.1.2019
    5 -11 10.5.2019 29.4.2019
    6 257 5.12.2018 19.8.2019

     

     



  • 2.  RE: Calculate the Number of Days Between Two Dates

    Employee
    Posted 03-11-2020 06:23

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    The Aggregate you're using is probably grouped by the CUSTOMER_ID but your order dates might not be in order? I would sort the data by CUSTOMER_ID and OrderDate, then group it by CUSTOMER_ID so you know the firstInGroup will be the first order and lastInGroup will be the latest order from that customer. Attached is an example that I think will help.

     

    Attached files

    Customer Order Dates - 11 Mar 2020.lna