I'm really stuck on a problem with aggregating data.
So what I have is a bunch of facilities that have done a number of assessments at a given time. So what I've done so far is count all the assessments done in a particular facility up to a certain date or period (past day, week, month, etc). Then what I do is get the average number of assessments for n periods back before that date.
For example, lets say I have something like this:
Period = week
N Periods = 4
# of total assessments past week = 15
# of total assessments 1 week before the first week (so # of assessments from 2 weeks ago to 1 week ago) = 16
# of total assessments 2 weeks before first week = 14
# of total assessments 3 weeks before first week = 10
# of total assessments 4 weeks before first week = 12
Avg assessments of N (or 4) periods would be (16+14+10+12)/4 = 13
Then what I do is calculate the difference between the recent # of assessments to the average historical # of assessments
So I have 15 - 13 = 2
I do this calculation for several facilities. So I could have numbers like 3, 15, -2, 3, -12, etc.
What I do is I categorize each facility with either "Good" or "Bad" depending on if they have a positive value for the delta.
So this is where my problem begins. I want to create a pie chart which counts the number of "Good" facilities and "Bad" facilities.
But there is also something else, every facility belongs to an organization, and those organizations belong to partners.
So I want to also be able to to find the good and bad for those categories. (Is the aggregation of all assessments for a particular facility Good? Is the aggregation of all assessments for a particular Organization or Partner Good?, etc).
What I have is something like this:
Recent Assessment Count =
DATEADD([Period], -1, [Today Fake]) < [Assessment Created At]
[Assessment Created At] <= [Today Fake],
[Assessment Id], NULL))
[Period] = Period timeframe (day, week, month, quarter, year)
[Today Fake] = Temporary variable to select todays date (I'm looking at some old data)
[Assessment Created At] = The day the assessment was done
[Assessment Id] = The Unique identifier for the assessment
So Recent Assessment Count counts the total assessment done in the past day, week, month, etc.
Then what I have is:
Historical Assessment Average Count =
[Period], -[Number of Periods] - 1, [Today Fake]) <= [Assessment Created At]
[Assessment Created At] <= DATEADD([Period], -1, [Today Fake]),
[Assessment Id], NULL))/[Number of Periods]
[Number of Periods] = The number of periods to look back from the recent period
So Historical Assessment Average Count calculates the average assessments done over N periods.
Then what I do is calculate the Delta Assessment Count =
[Recent Assessment Count] - [Historical Assessment Average Count]
which will yield positive and negative numbers for every facility, organization, or partner, which then categorizes them into either "Good" or "Bad"
So then i create something called Assessment KPI =
IIF([Delta Assessment Count] >= 0, "Good", "Bad")
And at this point honestly I thought I could do something like
COUNT(IIF([Delta Assessment Count] >= 0, "Good", "Bad")) and then select Facility, Organization, or Partner as the dimension, but I get an error saying:
Argument to Count (an aggregate function) is already an aggregation and cannot be further aggregated
What I would like to do is create a pie chart that counts all the facilities/orgs/partners who are good/bad.
I'm fairly new to tableau so maybe there's something I'm misunderstanding but this is my current issue. If there's any more information required, I will try to provide it.
Any help would be greatly appreciated!