Help with calculation in Variable

[#1]

Hello

I have created series of variables used in the following scenario:

• count work requests which have not yet gone past the escalation completion date
• count work requests that are upto 4 weeks past the escalation completion date ... and so on!

these are as follows:

work request before escalation date: =count(if(vCurrentDate-date_escalation_completion<=0, wr_id))

work request upto 4 weeks after escalation date: =count(if(vCurrentDate-date_escalation_completion>0and vCurrentDate-date_escalation_completion<=28, wr_id))

I've then used these in a Text Object to show the count/result as follows: =num($(vWR_LessThanCompDate))

These work ok, so far!  The proble is they count all work requests (both open and closed) I only want them to count the open work requests.  So this would be where status_category={'open'} etc.  But I'm having trouble adding this part without breaking my variable.

I've done this as a variable as i will reuse it in expression in bar charts (stacked subtype) etc and as simple counts in text objects etc.

As well as showing the count, i also want to be able to show this as a percentage of open work request, for example:  the percentage of open work requests before escaltion date, and so on... so I'm guessing I'll need a different variable (and calculation) to work this bit out.

Can anyone help with the above two points?

Thanks

Dan

[Gysbert_Wassenaar]

Try =count({<status_category={'open'}, date_escalation_completion={'>$(vCurrentDate)'} >} wr_id)

For the percentage perhaps something like this:

count({<status_category={'open'}, date_escalation_completion={'>$(vCurrentDate)'} >} wr_id) /

count({<status_category={'*'}, date_escalation_completion={'>$(vCurrentDate)'} >} wr_id)

[#3]

If you can post a sample document I'll show you exactly how you can optimize your set analysis.

Thanks.

[#4]

Count({<status_category={'open'}, date_escalation_completion={'>$(vCurrentDate)'} >} wr_id)

[#5 · Accepted solution]

Above suggestions didn't solve it it, but helped me towards solution that has worked:

Work Request count:
=count({<status_cat={'open'}>}if(vCurrentDate-date_escalation_completion<=0, wr_id))

Percentage calculation:

=count({<status_cat={'open'}>}if(vCurrentDate-date_escalation_completion<=0, wr_id))
/
count({1<status_cat={'open'},wr_id=E({<prob_type={'preventive maint'}>})>}wr_id)