4 Replies Latest reply on Nov 9, 2017 8:59 AM by Chris Andreou

    calculated fields based on value of quick filter

    Chris Andreou

      Hi,

       

      I am creating a dashboard that shows performance of team-members. Each team-member belongs to a team.

       

      The dashboard has quick filters: Team, and Team-member

       

      The dashboard needs to show:

      When a specific Team-member is selected in the quick filter: show the selected Team-member’s performance against the Team-member’s target

      When a specific Team is selected and ALL is selected in the Team member quick filter: show the team’s performance against the Team’s target.

       

      I was able to get this working by setting the targets in a calculated field which looks like:

       

      IF     WINDOW_MIN( MIN( [Team member Id] ) ) =WINDOW_MAX( MAX([Team member Id]) ) THEN MAX([Team Member Target])

      ELSEIF WINDOW_MIN( MIN( [Team member Id] ) )<>WINDOW_MAX( MAX([Team member Id]) ) AND

             WINDOW_MIN( MIN( [Team Id] ) )=WINDOW_MAX( MAX([Team Id]) ) THEN THEN MAX([Team Target])

      ELSE null

      END

       

      This will return the team member’s target when only 1 team member is in the data set, and will return the team’s target if more than one team members are in the data set.

       

      This works fine in most cases, except in the following scenario:

      The user selects in quick filter ALL team members, but there is only 1 team member in the data set because there is no data yet for the other team members in that team.

      According to my target formula the target displayed is the team-member’s target. But in this case I would like the Team target to be displayed.

       

      Ideally I would like to create a calculated field that reads what the quick filter value is. i.e. If quick filter team member is ALL then display Team target.. but as I understand it is not possible to read the quick filter value?

      Any ideas on how I could resolve this issue?

       

      Many thanks,

      Chris

        • 1. Re: calculated fields based on value of quick filter
          Vishal Jethwa

          Chris,

           

          Could you not add an additional criteria to also check the size of the team by using count and seeing if its more than 1, and if not then use the Team Target for the team member

          • 2. Re: calculated fields based on value of quick filter
            Chris Andreou

            I could, but I think I would end up with the same problem.

             

            For example:

            The user has selected ALL team members in the quick filter, because they need to see how the team is performing against the Team targets

             

            But there is only 1 team member in the data set because there is no data yet for other team members.

            The count of Team Members in my data set will be 1 - so the target for this Team-member will be returned which in this case will not be correct.

             

            So I guess the question is:

            How can I differentiate between:

            1. There is 1 Team Member in my data set because the user has selected this specific Team Member in the quick filter -> Display Team Member's target.

            2. There is 1 Team Member in my data set only because there is no data from other Team Members -> Display Team's target.

             

            Hope the above makes sense.

             

            Thanks

            Chris

            • 3. Re: calculated fields based on value of quick filter
              Vishal Jethwa

              Is there a way you could supply a packaged workbook?

              • 4. Re: calculated fields based on value of quick filter
                Chris Andreou

                Hi Vishal,

                 

                I have attached a workbook.

                What I am trying to achieve is: whenever the user selects in quick filter Team Members:ALL then the Team target (2000) is displayed so they can see how the whole team is performing against the team target.

                And when they select a specific team member to see the target for that specific team member.

                 

                In quick filters if you select month: February and Team Members: ALL  the Team target (2000) is displayed which is what I want.

                But if you select month March and Team Members: ALL,  it returns the target for that team member (because there is data only for 1 team member for that month)

                 

                 

                 

                Thanks,

                Chris