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

[#1]

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

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

[#3]

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

[#5]

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

[#7]

Unfortunately, nothing.

[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?

[#9]

Hi Suni,

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

Regards,

Myles