topic Conditional Where Statement in QlikView

Conditional Where Statement

Mon, 21 Jul 2014 19:25:46 GMT

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

Anonymous — Mon, 21 Jul 2014 19:35:24 GMT

Hi Toedtli,

Could you please provide an example.

Regards,

Re: Conditional Where Statement

joshabbott — Mon, 21 Jul 2014 19:54:08 GMT

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

gmoraleswit — Mon, 21 Jul 2014 19:59:21 GMT

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