Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
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