I found this formula in The specified item was not found.:
([AuditDelivery_DTTM]-[Pickup_DTTM])- if (DATEDIFF('year',[Pickup_DTTM], [AuditDelivery_DTTM]))>=1 then (2*((DATEPART('week', [AuditDelivery_DTTM]) + 52*DATEDIFF('year',[Pickup_DTTM], [AuditDelivery_DTTM])) - DATEPART('week', [Pickup_DTTM]))) else (2*(DATEPART('week', [AuditDelivery_DTTM]) -DATEPART('week', [Pickup_DTTM]))) end
The formula crashed my Tableau 8.0.4. Then I deleted all worksheets and created a new only containing involved fields. That helped. See attached. Hopefully this formula will not crash on your computer.
cpc_deliverymetrics - kettan.twbx 444.2 KB
Thanks Kettan. The calculation of the day count works but only if I wanted to use both the Pickup Date & Delivery date as dimensions in my report. I'd have to show every combination of pickup/delivery date. What I'm trying to do is use Pickup Date in the row and show for that specific pickup date, how many records have a day count of 1, #of records with day count of 2, etc. This may be useful for a different report but not for this one.
You can correct me if I am wrong, but I think this calculation will get you to where you need to be:
DATEDIFF('weekday',[Pickup_DTTM],[AuditDelivery_DTTM] ) - (IF DATEPART('weekday',[AuditDelivery_DTTM])=7 THEN 1 ELSE 0 END) - 2 * datediff('week',[Pickup_DTTM],[AuditDelivery_DTTM])
Verify this against your data and let me know if this works as you expect it to. The IF statement is meant to handle Saturdays since the datediff week calculation will not catch Saturdays since the weeks start on Sundays.
I hope this helps,
That worked perfectly Dan. Thanks for your help.
Glad to hear it. Happy to help when I can.