-
1. Re: Legend based on different level data
Norbert MaijoorDec 8, 2016 2:16 AM (in response to yakis.yak)
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
-
Legend.twbx 41.7 KB
-
-
2. Re: Legend based on different level data
Sujay Paranjpe Dec 8, 2016 2:25 AM (in response to yakis.yak)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
-
Legend.twbx 54.3 KB
-
-
3. Re: Legend based on different level data
yakis.yak Dec 8, 2016 3:49 AM (in response to Norbert Maijoor)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
-
Legend 2.twbx 54.2 KB
-
-
4. Re: Legend based on different level data
Norbert MaijoorDec 8, 2016 4:01 AM (in response to yakis.yak)
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....??