Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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
Author

Hi Suni,

Thank you for the message.

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

sunny_talwar

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
Author

Yes, still giving an error.

'Error in Set Modifier ad hoc element list'

Regards,

Myles

sunny_talwar

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
Author

Unfortunately, nothing.

Code Example.PNG

sunny_talwar

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
Author

Hi Suni,

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

Regards,

Myles