CODEWITHSUNDEEP
tableau-apicalculated-field
em456
em456

Reputation: 433

calculated field in tableau dependent on values in column

I'm new to Tableau so could have this all wrong, i'm trying to create a calculated field which works out the percentage of 'completed' tasks.

My data looks like

Date |   Department | Task |  Task Status       |  Number of Items
5/11      HR           one    one (complete)        5
5/11      HR           one    one (in progress)     3
5/11      HR           one    one (not started)     1
5/11      HR           two    two (complete)        3
5/11      HR           two    two (in progress)     6
5/11      HR           two    two (not started)     9
5/11      Finance      one    one (complete)        3
5/11      Finance      one    one (in progress)     2
5/11      Finance      one    one (not started)     8

I'm trying to work out the percentage of completed tasks for each department so for example in HR it is 8/27 tasks are completed. This data is updated every week so i'd like to know it for the most recent date. There are only 2 tasks. What I have so far is

IF [Date] == #05/11# 
THEN 
SUM(IF [Task Status] == 'one (complete)' OR [Task Status] =='two (complete)' THEN [Number of Items] 
END)
/SUM([Number of Items]
END

However it is throwing a 'Cannot mix aggregate and non-aggregate comparison or results in IF expressions.' error

can anyone point me in the right direction?

Upvotes: 1

Views: 811

Answers (2)

AnilGoyal
AnilGoyal

Reputation: 26238

You are working at level of aggregation shown at level of detail. Therefore you need to add LOD calculated field here. Do it like this-

{FIXED [Date], [Department] : sum(IF [Task Status] = 'one (complete)' OR [Task Status] ='two (complete)' THEN
[Number of Items] End)}/{FIXED [Date], [Department] : sum([Number of Items])}

sample data given

enter image description here

Adding this field in front of date and department column, in a view, will give you desired ratio.

enter image description here

Good luck

p.s. this will give you ratio for every date. If you need this ratio for one date, filter out other dates from view

Upvotes: 1

Andy TAR Sols
Andy TAR Sols

Reputation: 1735

2 options.

  1. Wrap [Date] in ATTR(), so it would be IF ATTR([Date])=...
  2. Rewrite your calculation and make use of boolean calculations:

SUM(INT([Date] == #05/11# AND RIGHT([Task Status],10)="(complete)"))/SUM(INT([Date] == #05/11#))

Upvotes: 0

Related Questions