topic Set analysis - Convert from table to chart in QlikView

Set analysis - Convert from table to chart

Anonymous — Mon, 21 Sep 2015 09:02:06 GMT

I currently have a table which contains a number of field and I have applied various filters to get the desired data. As I need to create some charts I need to change this to set analysis so I can summarise the data.

My current syntax is :

if(VacProgress='In Progress' and

(vacstate<>'Draft' and vacstate<>'In progress'and vacstate<>'Refused') and

not wildmatch(empjobref, '*VCP*'),

count(distinct esrempjobref))

Can anyone help me change this expression so it works with set analysis?

Many thanks

Phil

Re: Set analysis - Convert from table to chart

sunny_talwar — Mon, 21 Sep 2015 09:14:46 GMT

May be this:

=Count(DISTINCT {<VacProgress = {'In Progress'}, vacstate -= {'Draft', 'In Progress', 'Refused'}, empjobref = {'*VCP'}>} esrempjobref)

Re: Set analysis - Convert from table to chart

Anonymous — Mon, 21 Sep 2015 09:23:44 GMT

Sunny

That doesn't work I am afraid.

Maybe something to do with a '-' before vacstate. Also the the wildcard need to be NOT including 'VCP'

Thoughts?

Screen shot below

Re: Set analysis - Convert from table to chart

avinashelite — Mon, 21 Sep 2015 09:26:02 GMT

try like this:

Count(DISTINCT {<VacProgress = {'In Progress'}, vacstate =E({$<vacstate ={''Draft', 'In Progress', 'Refused''}>}vacstate )

, empjobref =E({$<empjobref ={'*VCP*'}>}empjobref )>} esrempjobref)

Re: Set analysis - Convert from table to chart

sunny_talwar — Mon, 21 Sep 2015 09:26:27 GMT

Although you see red lines underneath the expression, there might be a good chance that you may still get your solution. The red lines are shown in older versions of QlikView when you use -=. Can you check if after clicking okay do you get a result you expect?

Re: Set analysis - Convert from table to chart

sunny_talwar — Mon, 21 Sep 2015 09:27:25 GMT

Try this:

=Count(DISTINCT {<VacProgress = {'In Progress'}, vacstate -= {'Draft', 'In Progress', 'Refused'}, empjobref -= {'*VCP'}>} esrempjobref)

Re: Set analysis - Convert from table to chart

Anonymous — Mon, 21 Sep 2015 09:47:12 GMT

Sunny

That's nearly it !

It works when I have the same full table, but when I remove the dimensions it loses data.

Thoughts?

Phil