topic Re: Expression in % in QlikView

Expression in %

amber2000 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Expression in %

Anil_Babu_Samineni — Mon, 03 Apr 2017 14:24:54 GMT

For first output you may need this?

=sum({$<[Jobcode]={"*"}-{"ILUNP"}>} if([Aisle Zone]='FORK' and [Jobcode]<>'IINCH' and [Jobcode]<>'DPURP' and [Action Code] <> 'PM' ,[Assignment Replenish Time], [Assignment Time Worked]-[Assignment Beginday Difference]))

And second one are you talking about %PP?? If so, What value you are expecting to see

Re: Expression in %

amber2000 — Mon, 03 Apr 2017 14:53:58 GMT

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%

Re: Expression in %

sunny_talwar — Mon, 03 Apr 2017 15:12:24 GMT

May be like attached

Re: Expression in %

aarkay29 — Mon, 03 Apr 2017 15:35:31 GMT

PFA

Updated with PP%

Thanks,

Aar

Re: Expression in %

amber2000 — Mon, 03 Apr 2017 15:42:43 GMT

Thanks Sunny,

Do you also Know how to handle the expression that's needed for %PP please?

Monique

Re: Expression in %

sunny_talwar — Mon, 03 Apr 2017 15:47:37 GMT

Check now

Re: Expression in %

amber2000 — Mon, 03 Apr 2017 15:50:21 GMT

Thanks Sunny,

This is what I wanted, and actually this isn't so difficult.

Kind regards,

Monique

Re: Expression in %

amber2000 — Mon, 03 Apr 2017 15:52:08 GMT

Thanks Aar,

Your solution is the same as Sunny's.

I would like to thank you all for helping me.

You guys are great

Monique

Re: Expression in %

amber2000 — Mon, 03 Apr 2017 16:53:57 GMT

Sunny,

I'm sorry but with one record everything looked good.

But there is still something not quit wright, I've added a few more records to see the difference.

If I select nothing so the %PP should give me 100% per record. (It's not doing that, the 100% is splitted into 5 values, 1 value per record)

Employee Badge	#Time Worked	#Colli	#Assignments	Average Assignments	Productivity	Target	Percentage	%PP
2635	7:47:00	463	11	42,09	59,49	185,20	32,12%	21,66%
4103	7:45:26	392	14	28,00	50,53	148,40	34,05%	21,59%
4708	5:09:36	834	19	43,89	161,63	193,14	83,69%	14,36%
5139	7:42:37	539	22	24,50	69,91	137,20	50,95%	21,45%
9124	7:31:35	1.601	44	36,39	212,72	163,74	129,91%	20,94%

When I select Productive then the % changes to less:

Employee Badge	#Time Worked	#Colli	#Assignments	Average Assignments	Productivity	Target	Percentage	%PP
2635	2:08:15	463	8	57,88	216,61	214,14	101,15%	5,95%
4103	2:09:03	392	11	35,64	182,25	160,36	113,65%	5,98%
4708	5:02:11	834	18	46,33	165,59	194,60	85,09%	14,01%
5139	3:37:47	539	19	28,37	148,50	147,52	100,66%	10,10%
9124	6:51:30	1.601	41	39,05	233,44	175,72	132,85%	19,08%

For EmployeeBadge the Productive %PP should be 27,46% and whatever the selection I make it's not showing the correct result.

In the Main tab the result of %PP is also different, it should show the same result (here the dimension is Department instead of EmployeeBadge

Do you know what's wrong?

Monique

Re: Expression in %

sunny_talwar — Mon, 03 Apr 2017 16:59:13 GMT

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?

Re: Expression in %

amber2000 — Mon, 10 Apr 2017 14:24:55 GMT

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

Re: Expression in %

amber2000 — Tue, 11 Apr 2017 14:09:38 GMT

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

Re: Expression in %

sunny_talwar — Tue, 11 Apr 2017 14:18:58 GMT

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

Re: Expression in %

sunny_talwar — Tue, 11 Apr 2017 14:21:34 GMT

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

Re: Expression in %

amber2000 — Tue, 11 Apr 2017 15:01:54 GMT

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.

Re: Expression in %

sunny_talwar — Tue, 11 Apr 2017 15:05:40 GMT

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

Re: Expression in %

amber2000 — Tue, 11 Apr 2017 15:08:16 GMT

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?