CODEWITHSUNDEEP
group-bytableau-api
Cor Rad
Cor Rad

Reputation: 1

Tableau : how to do sql equivalent of group by and partition

newbie here; have spent almost a day reading various websites and tips but still getting no where

how does one do a sql equivalent of group by and obtaining only 1 row per partition in tableau?

Basically I have a table of the following, and I am trying to calculate the average duration. Average duration is taking the difference between startdate and enddate of a call and averaging all calls. Partition would be the call id

 Call_ID  services     StartDate             EndDate
    1       A           10:00am              10:30am
    1       B           10:00am              10:30am
    2       A           10:42am              10:52am
    2       B           10:42am              10:52am
    2       C           10:42am              10:52am

In the above example, the average duration would be 20 minutes Right now, I can calculate the duration for each ROW

DATEDIFF('minute', [StartDate], [EndDate])

Tableau would show the average as 18 minutes, which is not correct.

I am stuck at how to obtain only one duration per Call ID.

Any assistance is really appreciated

Upvotes: 0

Views: 2605

Answers (1)

Bernardo
Bernardo

Reputation: 3318

With the data sample you provided, the following would result in an average of 20 minutes.

avg({fixed [Call Id]: AVG(DATEDIFF('minute', [Start], [End]))})

This uses Level of Detail function. A new feature of Tableau 9.0 and up.

Upvotes: 1

Related Questions