CODEWITHSUNDEEP
sqlcounttableau-apicalculated-fieldmaxdate
Geethika Gayatri Padala
Geethika Gayatri Padala

Reputation: 1

Calculating the percentage difference between today's patients count with yesterday's patient count in Tableau

My data contains patient ids (around 70) and their time and date of visit. The dates are from April 1 to April 15. I want to calculate the percentage difference between the patients of today vs yesterday in calculated fields in Tableau.

The formula I've been trying is:

({FIXED : COUNTD(IF DATEPART('day', [Date]) = MAX(DATEPART('day', [Date])) THEN [Patient ID] END)})

but I'm facing an error.

Upvotes: 0

Views: 48

Answers (1)

Superman
Superman

Reputation: 462

First, identify the total count of distinct patients for each day and then calculate the percentage difference between the count of patients on the current day and the previous day.

You can follow the step-by-step approach like this:

  1. Create a calculated field to count distinct patients for each day and let's call this calculated field Distinct Patient Count: { FIXED [Date] : COUNTD([Patient ID]) }

  2. Create a calculated field to get yesterday's date relative to today and call this calculated field Yesterday's Date: TODAY() - 1

  3. Now create a calculated field to get the distinct patient count for today and call this calculated field Today's Patient Count: { FIXED : COUNTD(IF [Date] = TODAY() THEN [Patient ID] END) }

  4. Similarly, create a calculated field to get the distinct patient count for yesterday and call this calculated field Yesterday's Patient Count: { FIXED : COUNTD(IF [Date] = [Yesterday's Date] THEN [Patient ID] END) }

  5. Finally, create a calculated field to get the percentage difference between today's and yesterday's patient count and call this calculated field Percentage Difference: ([Today's Patient Count] - [Yesterday's Patient Count]) / [Yesterday's Patient Count] * 100

If there's a chance that there are no records for today or yesterday, you might want to include checks to avoid dividing by zero or other errors. Ensure that your date field is correctly formatted, and all fields used in your calculations are in the right context within Tableau (such as filters or fixed scopes). If there are any gaps or incorrect date formats, it might impact the calculations.

Upvotes: 0

Related Questions