9 Replies Latest reply on Aug 29, 2016 5:37 AM by Benjamin Greene

Count recurrences by Date and Type

Hello Tableau community,

I am trying to calculate the number of recurrences by Absence type and their count. For an example:

ID 6001 has 6 counts of ATUREP-F and a total number of discrete occurrences are 3. (The first incident on Jan 6 & 7, the second on March 21 and the third happened on March 18,19 and 20). I will consider as one recurrence if there is a consecutive date. I have attached the sample spreadsheet. Please let me know if you need more explanation. Thanks in advance

Goraya

• 1. Re: Count recurrences by Date and Type

Someone asked a similar question earlier today. See below:

Re: Help with creating a calculated field to aggregate sequential dates for hospital admissions

The key is to use table calculations to create an indicator for the first day of each occurrence, then sum them.

• 2. Re: Count recurrences by Date and Type

Here's an idea. The solution is in the first sheet, and the second sheet is essentially the same thing, just with all the underlying data showing.

• 3. Re: Count recurrences by Date and Type

I am getting an error when using the described formula. Thanks

• 4. Re: Count recurrences by Date and Type

You appear to be missing a ")" at the end for that formula to work.

• 5. Re: Count recurrences by Date and Type

Sorry, it does not help. I tried it three ways

PREVIOUS_VALUE(1) + IIF(DATEDIFF('day', LOOKUP([Date], -1), MIN([Date]) > 1, 1,0")"

PREVIOUS_VALUE(1) + IIF(DATEDIFF('day', LOOKUP([Date], -1), MIN([Date]) > 1, 1,0)"

PREVIOUS_VALUE(1) + IIF(DATEDIFF('day', LOOKUP([Date], -1), MIN([Date]) > 1, 1,0")

Unterminated string error

Thanks

GGoraya

• 6. Re: Count recurrences by Date and Type

Try

PREVIOUS_VALUE(1) + IIF(DATEDIFF('day', LOOKUP([Date], -1), MIN([Date]) > 1, 1,0))

Whenever you are writing a more involved formula, I would recommend counting the number of opening parentheses and closing parentheses to make sure they match. Also, you don't need any quotes around the closing parenthesis - I added those in the forum post just to make it clear that it was the ) character that you needed to add.

• 7. Re: Count recurrences by Date and Type

Sorry about that. Now I am getting another error. Hopefully, you help me to fix it too. Thanks

• 8. Re: Count recurrences by Date and Type

Thank you Benjamin for the help.

When I tried to apply "discrete" formula to the main worksheet, it does not show the accurate values. Can you please provide me a general idea how this calculate (aggregate) days or any other generic formula that can help. Thanks

Gurpreet

• 9. Re: Count recurrences by Date and Type

Could you provide an example of an instance where one of the values isn't right, and what the correct value would be? This calculation is based on the logic you described in the original question. However, the Total, Discrete and Filter fields are all table calculations that will only work correctly if they are computed the right way. After dragging the fields into the view, you will need to right click each of them and select Edit Table Calculation, select Compute using Advanced, make sure that the calculation is Partitioning using Absence Type and Addressing Date and ID. Then click OK and make sure At the level is set to Deepest and Restarting every is set to None. Make sure all these settings are correct and then check to see if the numbers are right.