CODEWITHSUNDEEP
calculationtableau-desktop
user18709081
user18709081

Reputation: 15

Looking to create a daily average calculation in tableau where the number of appointment dates are fixed

To get daily average, we use the (number of visits)/(number of business days). Here is my issue, visits(numerator) can occur on weekends days however, we want our denominator (business days) to be a fixed number in which it doesn't include weekends or holidays(but keep visits that occur on weekends). So I cannot simply just filter out dates without losing visits that occur on weekends, and if I include weekends then it skews our number of business days. Is there anything I can do to ONLY count the number of business days limited to Monday through Friday & no weekends (for the denominator), without filtering dates (so that it does not affect the numerator)? I tried creating a "Business Day" field, but not sure how this could be used in the calculation again without it being filter.

My attempt below, figured if I could create a calculated field then I could just divide below by number of visits

IE. IF [Business Day] = 'Business Day' THEN COUNT([VISIT]) END Error Message: Cannot Mix aggregate and non aggregate comparisons or results in If Expressions

Upvotes: 0

Views: 312

Answers (1)

remsky
remsky

Reputation: 131

Believe the issue is that you're trying to mix aggregated/non-aggregated fields in the same calc (comparing # of visits aggregation in the if statement against a non-aggregated [Business Day]). What you can try is creating 3 Calculated Fields for the result you're after. You might have to play around with it

Number # of visits

[Number of Visits] = COUNT([VISIT])

Number of Business Days: excluding Sun[1] & Sat[7]

[Number of Business Days] = 
SUM(
    CASE DATEPART('weekday', [Date])
        WHEN 1 THEN 0
        WHEN 7 THEN 0
        ELSE 1
    END
)

To get your Average Number of Visits per Business Day

[Average Visits per Business Day] = [Number of Visits] / [Number of Business Days]

Upvotes: 1

Related Questions