Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
Not applicable

Help with supressing employee name within a bar chart (stacked)

Hello all,

I have a bar chart that I created that looks at two things: employee name and time it takes to complete a stage of the project. There are four stages to the project that I have combined to form a stacked bar chart.

My question today relates to help on supressing certain employee names within my chart. There are several employees that I would wish to exclude from showing the results within this chart.

Can anyone help with this?

My Script within the chart expression that combines my stages is:

=num(if(IsNull(avg({$*<ProjectType={'StageB}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)), 0, avg({$*<ProjectType={' StageC '}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +
if
(IsNull(avg({$*<ProjectType={' StageD '}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)), 0, avg({$*<ProjectType={' StageD'}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime
)) +
if
(IsNull(avg({$*<ProjectType={' StageA '}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)), 0, avg({$*<ProjectType={'StageA'}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime
)) +
if
(IsNull(avg({$*<ProjectType={'StageC'}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)), 0, avg({$*<ProjectType={'StageC'}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)), '#,##0')


Regards,

Myles

9 Replies
agomes1971
Valued Contributor II

Re: Help with supressing employee name within a bar chart (stacked)

Hi,

maybe using section access

section access and application access

HTH

André Gomes

Partner
Partner

Re: Help with supressing employee name within a bar chart (stacked)

I would create a new field in my reload script and use this as a dimension in the chart instead of the employee. The employees you need to eliminate could have a null value in that new field :

Employee     EmployeeNew

John     John

George null

Sam     Sam

Maria     null

Not applicable

Re: Help with supressing employee name within a bar chart (stacked)

Thanks for the response Ioannis.

Is there a direction you could point me in to help with this?

Partner
Partner

Re: Help with supressing employee name within a bar chart (stacked)

An IF statement in your reload :

load

...

[Employee name],

if(condition, [employee name], null()) as [New Employee Name]

.....

resident [YourTable]

If you can post the script and a condition to determine who should be shown and who not, I could be of more help

Not applicable

Re: Help with supressing employee name within a bar chart (stacked)

Thanks for the quick response.

Before I get started wouldn't this exclude these individuals from all of my reporting? Or is there a way to only apply this to my one chart? I ask, because I wouldn't want to exclude these people because I have several other objects that they should be reported on.

Regards,

Myles

Partner
Partner

Re: Help with supressing employee name within a bar chart (stacked)

There are 2 fields to use if you implement this solution. Use any one in your charts according to the requirements, Employee as dimension when you need all employees and New Employee as a dimension if you only need the reduced ones.

Not applicable

Re: Help with supressing employee name within a bar chart (stacked)

Okay, so that works well for what I would like to do.

Now the only thing is I don't exactly have a condition. It's essentially I do not want to include x, y and z staff members.

Must there be an if statement? Or rather, could I simply request to only show/exclude the following individals a, b and c.

Partner
Partner

Re: Help with supressing employee name within a bar chart (stacked)

an if statement with condition : if(employee='x' or 'y' or 'z', null(), employee) etc.

Not applicable

Re: Help with supressing employee name within a bar chart (stacked)

Okay Ioannis I think I better understand the approach. You mentioned above I could make this much easier for you to help if I had a script to show you. And I assume in reference to how I currently get the Employee Field.

Let vPath='$(RootFolder)Live Data\Project Details';

[Project Details]:
LOAD [Project Number],
    
replace(mid([Project Number], 1, 3), '-', '') as [Project Type],
    
[Project Description],
    
[Rec Number],
    
if(   replace(mid([Project Number], 9, 4), '-', '')='PPJ'
     , 'New Initiative [OOSys]',
[Business Division]) as [Business Division],
    
Category,
    
[Stakeholder Rep],
    
[Reck Forwarded to Dept],
    
[Employee Assigned To],
    
[Employee Assigned To Date],
    
[Project Created Date],
    
[Project Coordinator],
    
[Estimated Value of Proj],
    
[Success Value],
    
[Priority],
    
[Project Status],
    
Date(Date#(replace(filebasename(), 'Project Details - ', ''), 'MMMM DD, YYYY'), 'M/D/YYYY') as [Project Details.Load Date],
    
Date(Date#(replace(filebasename(), 'Project Details - ', ''), 'MMMM DD, YYYY'), 'M/D/YYYY') as [Load Date%]    
FROM $(vPath)\*.xlsx
(
ooxml, embedded labels, table is Sheet1);

My Condition would be for the only field that I'm focusing on in the above being Employee Assigned To. The condition itself would be as you mentioned above, but to lament it in my a real world term:

=if(Employee Assigned to='David O' or 'Ricky B' or 'Bobby V', null(), employee)

Regards,

Myles