topic Re: Excluding Select Team Members from Script contained within Expression of Stacked Bar Chart in QlikView

Excluding Select Team Members from Script contained within Expression of Stacked Bar Chart

Tue, 23 Jun 2015 17:55:35 GMT

Hello all,

As mentioned I am working with a stacked bar chart (combined), which lists an average time it takes to complete each stage of a process. The only issue I have is we have some members who I would like to exclude from this list.

=num(if(IsNull(avg({$*<ProjectType={'Step1'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)), 0, avg({$*<ProjectType={' Step1'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +

if(IsNull(avg({$*<ProjectType={ ‘Step2'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)), 0, avg({$*<ProjectType={ Step2'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +

if(IsNull(avg({$*<ProjectType={'Step3'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)), 0, avg({$*<ProjectType={ ‘Step3'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +

if(IsNull(avg({$*<ProjectType={‘Step4'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)), 0, avg({$*<ProjectType={ ‘Step4'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),'#,##0')

Ideally, I was hoping to add to the expressions saying AND NOT [Employee Assigned To]='John Smith' AND NOT [Employee Assigned To]='Jane Smith'

Regards,

Myles

Re: Excluding Select Team Members from Script contained within Expression of Stacked Bar Chart

sunny_talwar — Tue, 23 Jun 2015 18:00:28 GMT

Why don't you add this to the set analysis:

[Project.Flag]={'0'}, [Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})

Re: Excluding Select Team Members from Script contained within Expression of Stacked Bar Chart

Tue, 23 Jun 2015 18:28:25 GMT

Hi Suni,

Thank you for the message.

I tried adding but it indicated an error in the set analysis.

Re: Excluding Select Team Members from Script contained within Expression of Stacked Bar Chart

sunny_talwar — Tue, 23 Jun 2015 19:59:31 GMT

This is giving you an error as well? Try it

=num(if(IsNull(avg({$*<ProjectType={'Step1'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>} ProjectTime)), 0, avg({$*<ProjectType={' Step1'}, [Project.Flag]={'0'},[Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>} ProjectTime)) +

if(IsNull(avg({$*<ProjectType={ ‘Step2'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>} ProjectTime)), 0, avg({$*<ProjectType={ Step2'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>} ProjectTime)) +

if(IsNull(avg({$*<ProjectType={'Step3'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>}ProjectTime)), 0, avg({$*<ProjectType={ ‘Step3'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>} ProjectTime)) +

if(IsNull(avg({$*<ProjectType={‘Step4'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>}ProjectTime)), 0, avg({$*<ProjectType={ ‘Step4'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>} ProjectTime)),'#,##0')

Re: Excluding Select Team Members from Script contained within Expression of Stacked Bar Chart

Wed, 24 Jun 2015 12:47:10 GMT

Yes, still giving an error.

'Error in Set Modifier ad hoc element list'

Regards,

Myles

Re: Excluding Select Team Members from Script contained within Expression of Stacked Bar Chart

sunny_talwar — Wed, 24 Jun 2015 12:50:31 GMT

Just noticed a * next to $ sign. don't really think we need that. Can you try this:

=num(if(IsNull(avg({<ProjectType={'Step1'}, [Project.Flag]={'0'}, [Project Status]={'Actual'},[Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>}ProjectTime)), 0, avg({<ProjectType={' Step1'}, [Project.Flag]={'0'},[Project Status]={'Actual'},[Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>}ProjectTime)) +

if(IsNull(avg({<ProjectType={ ‘Step2'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>} ProjectTime)), 0,avg({<ProjectType={ Step2'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>} ProjectTime)) +

if(IsNull(avg({<ProjectType={'Step3'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>}ProjectTime)), 0,avg({<ProjectType={ ‘Step3'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>} ProjectTime)) +

if(IsNull(avg({<ProjectType={‘Step4'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>}ProjectTime)), 0,avg({<ProjectType={ ‘Step4'}, [Project.Flag]={'0'}, [Project Status]={'Actual'}, [Employee Assigned To] = e({<[Employee Assigned To] = {'John Smith', 'Jane Smith'}>})>} ProjectTime)),'#,##0')

Also can you post the image of when you paste this expression in, so that if there are other errors, we can see them?

Best,

Sunny

Re: Excluding Select Team Members from Script contained within Expression of Stacked Bar Chart

Wed, 24 Jun 2015 13:03:00 GMT

Unfortunately, nothing.

Re: Excluding Select Team Members from Script contained within Expression of Stacked Bar Chart

sunny_talwar — Wed, 24 Jun 2015 13:07:12 GMT

It seems like your don't have a field called ProjectType in your application. Can you make sure there are no spelling errors or such?

Re: Excluding Select Team Members from Script contained within Expression of Stacked Bar Chart

Fri, 26 Jun 2015 15:08:44 GMT

Hi Suni,

No errors. I opted to change the name of fields before posting online.

Regards,

Myles