4 Replies Latest reply on Dec 8, 2016 4:01 AM by Norbert Maijoor

# Legend based on different level data

Hi,

I have few dashboards presenting data on different levels based on few tables with inner joins. In table i have status for segments, but now for project. For projects i need create calculation based on segments status. If status segments all green then Blue elseif status segments >=60% are green then Green elseif status segments>20% are red then red else Grey. I was trying on first place create flag for each status an then calculation with if for status but i have problem with aggregate that and few other methods but nothing works like i want.  Is something like that possible in Tableau or need to be done on DB level? I attached example workbook.

Thanks for help!!

• ###### 1. Re: Legend based on different level data

Hi Yakis,

Find my approach as reference below and stored in attached workbook version 9.3

a. For each colour: if [status]="Blue" then 1 END

b. Count Segments per Project: {fixed [Project]:COUNT([Segment])}

c. Colour Indicator:

if { fixed [Project]: sum([Status Green])}/[Count Segments]>=1 then 1

elseif { fixed [Project]: sum([Status Green])}/[Count Segments]>=0.6 then 2

elseif { fixed [Project]: sum([Status Green])}/[Count Segments]>=0.2 then 3

elseif { fixed [Project]: sum([Status Green])}/[Count Segments]<0.2 then 4 END

a.

• ###### 2. Re: Legend based on different level data

Hi Yakis,

Your target can be achieved by using simple LOD calculations. I have created 5 LOD calculations. First four calculate # of segemtn for each color per project. The last LOD calculates total number of segments per project.

[BLUE Segment Per Project]

{ FIXED [Project] : SUM(IF [status] = "Blue" THEN 1 ELSE 0 END)}

[Green Segment Per Project]

{ FIXED [Project] : SUM(IF [status] = "Green" THEN 1 ELSE 0 END)}

[Grey Segment Per Project]

{ FIXED [Project] : SUM(IF [status] = "Grey" THEN 1 ELSE 0 END)}

[Red Segment Per Project]

{ FIXED [Project] : SUM(IF [status] = "Red" THEN 1 ELSE 0 END)}

[Total Segment per Project]

{ FIXED [Project] : COUNTD([Segment])}

Finally, created a claculated filed as per the rules that you have laid down

[Project Status]

IF [Green Segment Per Project]/[Total Segments per Project] = 1 THEN "Blue"

ELSEIF [Green Segment Per Project]/[Total Segments per Project] >= 0.6 THEN "Green"

ELSEIF [Green Segment Per Project]/[Total Segments per Project] >= 0.2 THEN "Red"

ELSE "Grey"

END

On the PRoject worksheet, replace status with Project Status on color card and you are set.

P.S. Project 1 will be in Red state as it has only 4 greens out of total 7 which 57%.

Best,

Sujay

• ###### 3. Re: Legend based on different level data

Thanks for fast replay! Your solution working fine but i in my example i forget add two more thinks sorry for that. Segment is not last level there is a Typ and country in columns. Could also help with this?

Regards

• ###### 4. Re: Legend based on different level data

Hi Yakis,

What I understand you would like to know the "status" for each "Project-Segment-Typ-Country" combination and compare that with total amount of combination within a project. If x amount of total within a project  has status "green" you would like to colour the project....??