Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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

8 Replies

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

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'}>})

Not applicable

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

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

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')

Not applicable

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

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

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

Not applicable

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

Unfortunately, nothing.

Code Example.PNG

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

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?

Not applicable

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

Hi Suni,

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

Regards,

Myles