Analyze subset of data in table

I have data as below. Patients can have several visits of various types. Some visit types may repeat (Initial Visit is the exception).

Please Login or Register  to view this content.

I'm using Pivot Tables to analyze these data. How can I answer questions like:

1. Among patients who had Well-Being visits, how many had a Surgery visit? [The answer is 2 patients had Well-Being visits, 1 of whom has had a Surgery visit.]

2. What is the average number of days that passed between the Initial Visit and the Surgery Visit?

To answer questions like these via the pivot, I suspect I need to restructure the data somehow, but I'm not entirely sure of the best way of even if that's necessary.

Thank you for any suggestions.

Originally Posted by dilipandey

Hi Holmes,

you can achieve this using formulas as well, see the attachment:-

Thanks for this. Because I have other variables on which I may want to pivot the data (e.g., number of days between initial hx and surgery visit, by males vs. females), the formula approach may not be very scalable. I was hoping I could build into the pivot a calculated variable(s), which would automatically re-calculate based on the variables that are added to the pivot.