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.
Data_v10.4.twbx 44.4 KB
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.
Data2.twbx 37.4 KB
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