topic Re: Help with calculation in Variable in QlikView

Help with calculation in Variable

Wed, 05 Nov 2014 09:33:40 GMT

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

Re: Help with calculation in Variable

Gysbert_Wassenaar — Wed, 05 Nov 2014 09:42:53 GMT

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)

Re: Help with calculation in Variable

Wed, 05 Nov 2014 09:56:50 GMT

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

Thanks.

Re: Help with calculation in Variable

Wed, 05 Nov 2014 12:35:23 GMT

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

Re: Help with calculation in Variable

Wed, 05 Nov 2014 13:52:37 GMT

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)