3 Replies Latest reply on Dec 6, 2018 5:16 AM by Amit Narkar

# Calculate Number of Events between other Events depending on the Type of the Event

Dear Tableau Community,

following issue:

I have different cars that have inspections on certain days. Multiple inspection types are possible, the focus though is on L=large and S=small. I need to calculate the number of S inspections between the current and the last L inspection for each car. I have added the desired result to some data for a better understanding:

Those red numbers will be used to calculate the average number for each month. This is the desired output that needs to be shown in a dashboard:

I tried various calculated fields without getting close the desired result, so I will be happy if someone can help me here.

Best Regards

Marco

• ###### 1. Re: Calculate Number of Events between other Events depending on the Type of the Event

I could right some calculations to get end results, however I am confused regarding your expected value for car B

as stated earlier you are only looking for inspections 's' if there is any prior or later L inspection. in that case your count will differ for car B.

Please find attached workbook for your reference.

• ###### 2. Re: Calculate Number of Events between other Events depending on the Type of the Event

Dear Amit,

thanks a lot for your reply. Your are right, the first count "2" for car B is not correct, should be "0". I apologize.

In your solution you count the total amount of S and L for each car, as I understood. This provides an overall average. However, I need the data by month or week, just not the total. I modified the data and added another month for a better understanding:

Resulting in (as example) this view:

By using your approach of counting the totals for each month every interval covering two months (for example the three S occurding between 14.01. and 03.02. for car B) would get lost.

See attached modified tableau workbook.

Best Regards

Marco

• ###### 3. Re: Calculate Number of Events between other Events depending on the Type of the Event

Yes as calculations were fixed at level Car, they would give overall average. For monthly/weekly you may need to update LOD calculations to include more granularity datetrunc('month',date).

However currently I am not sure how to get first count '3' for Car A in Month Feb. with above logic.

Let see if someone else has some insights