topic expression set analysis calculation help! in QlikView

expression set analysis calculation help!

Tue, 04 Nov 2014 13:54:42 GMT

Need to some help to solve a frustrating little problem in an expression used to count the number of work requests!

I'm simplying trying to display of count of work requests based on a few simple criteria, one of which is causing me a small headache!!

I have two categorys of work request, "Preventative Maintenance" and "Reactive". Where reactive is made up of several problem types and preventative maintenace has only one problem type.

I have added an expression to text objects to count the number of work requests for "Preventative Maintenance" as follows:

all: =count( {1<prob_type ={'preventive maint'}>} wr_id)

open: =count({1<status_cat={'open'}, prob_type={'preventivemaint'}>}wr_id)

closed: =count({1<status_cat={'closed'}, prob_type={'preventivemaint'}>}wr_id)

But for the "Reactive" work requests its a little more complicated as I'm trying to count the work requests where the problem_type is NOT "preventive maint" (for "preventative maintenance").

This is because there are over 100 different problem_types that are classes are "Reactive" so logically it would be easier to count either all work that is "Preventative maintenace" or all work that is NOT "preventative maintenace"

So far for Reactive l have the following:

all: =count({$-1<prob_type={'preventive maint'}>} wr_id)

open: would be where status is "open" count all work requests where prob_type is not "preventive maint"

closed: would be where status is "closed" count all work requests where prob_type is not "preventive maint"

I need help to complete Open & Closed for reactive, as above?

In other systems (not qlikview) if i want all problem types that are not "preventive maint" i would use for example:

IS NOT "preventive maint" or

<> "preventive maint"

But it doesn't appear as simple as this in Qlikview, unless I'm missing something obvious

Dan

Re: expression set analysis calculation help!

rubenmarin — Tue, 04 Nov 2014 14:17:59 GMT

Hi Dan, you can use '-' (minus) in set analisys:

all: =count( {1<prob_type =-{'preventive maint'}>} wr_id)
open: =count({1<status_cat={'open'}, prob_type=-{'preventivemaint'}>}wr_id)
closed: =count({1<status_cat={'closed'}, prob_type=-{'preventivemaint'}>}wr_id)

Re: expression set analysis calculation help!

Tue, 04 Nov 2014 14:32:40 GMT

Thanks Ruben, thats helps - but also reminds me why I had the $-1 in the original count for all reactive work (=count({$-1<prob_type={'preventive maint'}>} wr_id) )

As there are 15 works orders (which are closed) that have no problem type, so the value is NULL... annoying I know, but the expression you've given doesn't count these NULL values from the problem_type and the reesult is 15 short of what it should be..., any ideas??

dan

Re: expression set analysis calculation help!

rubenmarin — Tue, 04 Nov 2014 14:45:54 GMT

Ok, nulls are allways tricky, you can use NullAsValue in script to set one value to nulls or maybe using exclusion:

all: =count( {1<wr_id=E({<prob_type ={'preventive maint'}>})>} wr_id)
open: =count({1<status_cat={'open'}, wr_id=E({<prob_type ={'preventive maint'}>})>} wr_id)
closed: =count({1<status_cat={'closed'}, wr_id=E({<prob_type ={'preventive maint'}>})>}wr_id)

Not tested.

Re: expression set analysis calculation help!

kuba_michalik — Tue, 04 Nov 2014 14:52:59 GMT

You have a good base set, now for open and close you need an intersect. This would be the set expression for "open" status:

{(1-1<prob_type={'preventive maint'}>)*1<status_cat={'open'}>}

Syntax coloring will probably break, but it should work

I've changed the $ to 1, if it's supposed to react to selections, $ would be appropriate.

Re: expression set analysis calculation help!

Tue, 04 Nov 2014 14:56:43 GMT

Thanks Ruben, thats got it.

I like the exclusion addition

Dan

Re: expression set analysis calculation help!

Tue, 04 Nov 2014 15:01:17 GMT

Also works, thank you