3 Replies Latest reply on Nov 11, 2018 3:51 PM by Shinichiro Murakami

    Find number of consecutive days

    Raoul Verhaegen

      Hi all,

       

      I really need help on something. So I've got 3 variables; date, MBD code and sales unit.

      What I need to get is the total sales unit for every MBD code for the last date event.

       

      However there is a twist! Sometimes an event can last 2 or more days.

      So what I need is a way to calculate the number of consecutive days between the very last date for this MBD code and the beginning of this event.

       

      For example, in the data provided, the last event for MBD code B has lasted 3 days, so I need a variable that will calculate the number of days between the last day (06/11/2018, European date) and the first day of the event (04/11/2018), so I can get the date range to compute my sales unit total.

       

      What I've been able to do so far is calculate the last and second last date for every MBD code, but an event can potentially last for any length of time, so if there is a way to calculate this without brute forcing, I'd be really grateful

       

      I've looked at existing threads like this one on customers with purchases in consecutive periods, and this one "find n consecutive occurences". However I'm wondering if this can be done wit an LOD instead of a table calculation ?

       

      Many thanks !

       

      Raoul