Expression in %

[amber2000]

Hi all,

I want to calculate the % of [#Time Worked] per [Jobcode Flag].

There are only 2 [Jobcode Flag] values: Productive and Non Productive

Jobcode Flag
Productive
Non Productive

When nothing is selected then the values for expression [#Time Worked] are displayed correctly for both [Jobcode Flag] fields.

Butnot for expression [=vTimeWorked], the total TimeWorked is showed there instead of x... time Productive and x... Time Non Productive.

Total Time Worked
Jobcode Flag	#TimeWorked	=vTimeWorked
	100%	7:42:37
Productive	47,08%	7:42:37
Non Productive	52,92%	7:42:37

This is the result I would like to see:  

Total Time Worked
Jobcode Flag	#TimeWorked	=vTimeWorked
	100%	7:42:37
Productive	47,08%	3:37:47
Non Productive	52,92%	4:04:50

When I select a [Jobcode Flag] Productive then the value of the expression #TimeWorked becomes 100% instead of 47,08%

If  I select Non Productive the same happens.

I have the same problem for this Table with expression %PP:

If I make a selection 'Productive' the value should be 47,08%, Non Productive should be 52,92% and if no selection is made then the value should be 100%

Assignments by employee
Employee Badge	#Time Worked	#Colli	#Assignments	Average Assignments	Productivity	Target	Percentage	%PP
5139	7:42:37	539	22	24,5	69,91	137,2	50,95%	-

Can anyone help me with this problem please? I'll attach a copy of the qvw to make it easy to so see what is going on.

Kind regards,

Monique

[sunny_talwar · Accepted solution]

Can you try this

=sum({$<[Jobcode]={"*"}-{"ILUNP"},[Employee Department]={"*"}-{"Bediende", "Carrefour", "Extern"}>} if([Aisle Zone]='FORK' and [Jobcode]<>'IINCH' and [Jobcode]<>'DPURP'

,[Assignment Replenish Time], [Assignment Time Worked]-[Assignment Beginday Difference]))

/

Sum(TOTAL <[Employee Badge]> {<[Jobcode Flag]>}Aggr(sum( {$<[Jobcode]={"*"}-{"ILUNP"},[Employee Department]={"*"}-{"Bediende", "Carrefour", "Extern"}, [Jobcode Flag]>} if([Aisle Zone]='FORK' and [Jobcode]<>'IINCH' and [Jobcode]<>'DPURP'

,[Assignment Replenish Time], [Assignment Time Worked]-[Assignment Beginday Difference])), [Employee Badge], [Jobcode Flag]))

If this doesn't work, would you be able to share an updated sample?

[amber2000]

Thanks a million Sunny,

Sorry for the late reaction but I was in England for a MS convention.

Your solution works like a charm.

Monique

[amber2000]

Hi Sunny,

There is still an issue with the totals of %PP, I need to include the Dimension [Employee Department] but if I add this dimension to the aggr it goes horrible wrong.

Now without Dimension [Employee Department] the total % should be a % relative to the Productive % but it shows 288,62%

I've attached a new example with some more data to see.

Employee Badge	#Time Worked	#Colli	#Assignments	Average Assignments	Productivity	Target	Percentage	%PP
Total	1085:57:48	187289	4944	37,88	172,46	170,47	101,17%	288,62%

Could you please advise what I need to do to get show the Total correctly please?

Kind regards,

Monique

[sunny_talwar]

What is the number that you are expecting to see here?

[sunny_talwar]

May be this:

=sum({$<[Jobcode]={"*"}-{"ILUNP"},[Employee Department]={"*"}-{"Bediende", "Carrefour", "Extern"}>}

  if([Aisle Zone]='FORK' and [Jobcode]<>'IINCH' and [Jobcode]<>'DPURP'

  ,[Assignment Replenish Time]

  , [Assignment Time Worked]-[Assignment Beginday Difference]))

/

Sum(TOTAL <[Employee Badge]> {<[Jobcode Flag], [Jobcode]={"*"}-{"ILUNP"},[Employee Department]={"*"}-{"Bediende", "Carrefour", "Extern"}, [Jobcode Flag]>}

  Aggr(sum( {$<[Jobcode]={"*"}-{"ILUNP"},[Employee Department]={"*"}-{"Bediende", "Carrefour", "Extern"}, [Jobcode Flag], [Employee Department]>}

  if([Aisle Zone]='FORK' and [Jobcode]<>'IINCH' and [Jobcode]<>'DPURP'

  ,[Assignment Replenish Time]

  , [Assignment Time Worked]-[Assignment Beginday Difference]))

  , [Employee Badge], [Jobcode Flag], [Employee Department]))

[amber2000]

I've tried this already but then I loose all the other expression values for most employees.

For example if I select employee 412 then all the expressions are filled in:

Employee Badge	#Time Worked	#Colli	#Assignments	Average Assignments	Productivity	Target	Percentage	%PP
Total	6:01:44	832	31	26,84	138,00	144,93	95,22%	14,18%
412	6:01:44	832	31	26,84	138,00	144,93	95,22%	14,18%

If I select no employee then for many of them the other expressions are blank.

The Total result though is shown correct.

[sunny_talwar]

May be using dimensionality() function

If(Dimensionality() = 0,

sum({$<[Jobcode]={"*"}-{"ILUNP"},[Employee Department]={"*"}-{"Bediende", "Carrefour", "Extern"}>}

  if([Aisle Zone]='FORK' and [Jobcode]<>'IINCH' and [Jobcode]<>'DPURP'

  ,[Assignment Replenish Time]

  , [Assignment Time Worked]-[Assignment Beginday Difference]))

/

Sum(TOTAL <[Employee Badge]> {<[Jobcode Flag], [Jobcode]={"*"}-{"ILUNP"},[Employee Department]={"*"}-{"Bediende", "Carrefour", "Extern"}, [Jobcode Flag]>}

  Aggr(sum( {$<[Jobcode]={"*"}-{"ILUNP"},[Employee Department]={"*"}-{"Bediende", "Carrefour", "Extern"}, [Jobcode Flag], [Employee Department]>}

  if([Aisle Zone]='FORK' and [Jobcode]<>'IINCH' and [Jobcode]<>'DPURP'

  ,[Assignment Replenish Time]

  , [Assignment Time Worked]-[Assignment Beginday Difference]))

  , [Employee Badge], [Jobcode Flag], [Employee Department])),

sum({$<[Jobcode]={"*"}-{"ILUNP"},[Employee Department]={"*"}-{"Bediende", "Carrefour", "Extern"}>}

  if([Aisle Zone]='FORK' and [Jobcode]<>'IINCH' and [Jobcode]<>'DPURP'

  ,[Assignment Replenish Time]

  , [Assignment Time Worked]-[Assignment Beginday Difference]))

/

Sum(TOTAL <[Employee Badge]> {<[Jobcode Flag]>}

  Aggr(sum( {$<[Jobcode]={"*"}-{"ILUNP"},[Employee Department]={"*"}-{"Bediende", "Carrefour", "Extern"}, [Jobcode Flag], [Employee Department]>}

  if([Aisle Zone]='FORK' and [Jobcode]<>'IINCH' and [Jobcode]<>'DPURP'

  ,[Assignment Replenish Time]

  , [Assignment Time Worked]-[Assignment Beginday Difference]))

  , [Employee Badge], [Jobcode Flag], [Employee Department])))

[amber2000]

For example the month april:

Productive and Non Productive = 100%

Productive stands for 43.30%

Non Productive for 56.70%

I will try to explane:

If I choose a Department then I want the Productive 43.30% to count for 100% Productive over the departments.

Then I want to see the Productive% per department.

Does this make a little bit sense?