3 Replies Latest reply: Jul 21, 2014 3:59 PM by Gabriela Morales RSS

    Conditional Where Statement

      I am trying to show the total number of projects in a table and then break that total down by Total projects won and total Projects Lost. I can get the Total Number of Projects to show but not the Total Won or Total Lost.  I would also need to break these down by percentages.

       

      Any thoughts?

       

      Thanks

        • Re: Conditional Where Statement
          Karla Rivas

          Hi Toedtli,

           

          Could you please provide an example.

           

          Regards,

          • Re: Conditional Where Statement
            Josh Abbott

            I'm assuming you want to use set analysis:

             

            Project - ID For Project

            Status - Won or lost, column in table

             

            Count({1} Distinct Project) = Total number of projects
            Count({1<Status={'Won'}> Distinct Project) = Total number of projects won

            Count({1<Status={'Lost'}> Distinct Project) = Total number of projects lost

             

            Replace the 'Project' and 'Status' columns above with the column names from your table.  This is done in the designer as an expression in your object, not in the script.

            • Re: Conditional Where Statement
              Gabriela Morales

              Hi,

               

              You can use set analysis to do this

               

              For total projects:

              count(projectid)

               

              For win projects (supposing fwin is a flag field where the value 1 is win and 0 is lost):

              count({$<fwin={1}>} projectid)

               

              For lost projects

              count({$<fwin={0}>} projectid)

               

              (you can also use 'distinct' with any of the count functions above)

               

              Hope this helps