Conditional Where Statement

[#1]

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

[Anonymous]

Hi Toedtli,

Could you please provide an example.

Regards,

[joshabbott]

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.

[gmoraleswit]

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