4 Replies Latest reply on Nov 15, 2018 6:40 PM by Raoul Verhaegen

# Find number of consecutive days

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

• ###### 1. Re: Find number of consecutive days

HI Raoul,

Hope this helps.

Steps

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Find number of consecutive days

Hi Shin,

Thanks a lot! Is it possible to come to the same results with a LOD calc?

• ###### 3. Re: Find number of consecutive days

Table calc is troublesome and want to avoid as much as possible.

But some type of calc need to use table calc.

I don't think you can use LOD in this case.

Shin

1 of 1 people found this helpful
• ###### 4. Re: Find number of consecutive days

Hi Shin,

Thanks for your help! In this case, I've decided to do the data wrangling in Tableau Prep so as to only have the dates that I want.

It's not exactly what I needed and it's a temporary fix but that'll do for now.

Thanks again