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

    Legend based on different level data

    Yakis Yak
    Yakis Yak Dec 8, 2016 1:38 AM

      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
          Norbert Maijoor
          Norbert Maijoor Ambassador Dec 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

           

           

           

           

          a.

          • 2. Re: Legend based on different level data
            Sujay Paranjpe
            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

            • 3. Re: Legend based on different level data
              Yakis Yak
              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

              • 4. Re: Legend based on different level data
                Norbert Maijoor
                Norbert Maijoor Ambassador Dec 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....??