% Calculation over multiple dimension

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

Hi everyone,

I'm struggling with an Aggregation in an expression (with set analysis) that should calculate the % over multiple dimensions.

In table Assignments by Employee the aggregation is done correct.

But in table % picking vs %non picking there are flaws.

Somewhere in the expression I've made a mistake but I don't see it anymore.

Can someone please help me?

I'll attach my document to make it more easy to see what's going on.

These are the required results:

Jobcode Flag	Jobcode Description	Aisle Zone Description	FlowType Description	#Time Worked	%PP
Productive	Case picking regulier	Flowthru	Pbl Traiteur	105:02:06	5,39%
Productive	Case picking regulier	Flowthru	Pbl Vlees	123:14:32	6,33%
Productive	Case picking regulier	Flowthru	Pbl Zuivel	241:12:00	12,39%
Productive	Case picking regulier	GRF 4de gamma	_Ongeldig	45:59:53	2,36%
Productive	Case picking regulier	GRF gekoeld	_Ongeldig	95:57:54	4,93%
Productive	Case picking regulier	PAT blok D	_Ongeldig	5:51:12	0,30%
Productive	Case picking regulier	Piece_Pick	_Ongeldig	2:46:53	0,14%
Productive	Case picking regulier	Piece_Pick_BF	_Ongeldig	6:37:14	0,34%
Productive	Case picking regulier	Zuivel gekoeld blok F	_Ongeldig	92:44:25	4,76%
Productive	Case picking regulier	Zuivel ongekoeld	_Ongeldig	54:11:53	2,78%
Productive	Case picking regulier	Zuivel Traiteur gekoeld blok H	_Ongeldig	23:14:58	1,19%
Productive	Case picking regulier	Zuurwaren/Mosselen	_Ongeldig	22:39:27	1,16%
Productive	Total			819:32:27	42,09%
Non productive	Activiteit PBL	_Ongeldig	_Ongeldig	2:09:00	0,11%
Non productive	Afvoer	_Ongeldig	_Ongeldig	1:06:16	0,06%
Non productive	Aministratie	_Ongeldig	_Ongeldig	84:44:45	4,35%
Non productive	Battery Change	_Ongeldig	_Ongeldig	1:46:46	0,09%
Non productive	Battery change	_Ongeldig	_Ongeldig	0:09:59	0,01%
Non productive	Begin day	_Ongeldig	_Ongeldig	340:01:33	17,46%
Non productive	Cleaning	_Ongeldig	_Ongeldig	45:21:50	2,33%
Non productive	Cleaning Groenten en fruit	_Ongeldig	_Ongeldig	7:19:46	0,38%
Non productive	End day	_Ongeldig	_Ongeldig	5:56:21	0,31%
Non productive	Garage	_Ongeldig	_Ongeldig	21:10:59	1,09%
Non productive	Input	_Ongeldig	_Ongeldig	23:49:11	1,22%
Non productive	Lading	_Ongeldig	_Ongeldig	16:58:06	0,87%
Non productive	Let Down PBL	_Ongeldig	_Ongeldig	8:34:44	0,44%
Non productive	Levelen Selectie	_Ongeldig	_Ongeldig	4:05:26	0,21%
Non productive	Opleiding	_Ongeldig	_Ongeldig	22:28:14	1,15%
Non productive	Opvolging PBL	_Ongeldig	_Ongeldig	53:32:11	2,75%
Non productive	Palletcontrole	_Ongeldig	_Ongeldig	19:11:38	0,99%
Non productive	Punting	_Ongeldig	_Ongeldig	44:22:06	2,28%
Non productive	RF Punting /Loading	FORK	_Ongeldig	130:24:13	6,70%
Non productive	RF putaway	FORK	_Ongeldig	29:59:44	1,54%
Non productive	RF replenishment	FORK	_Ongeldig	86:14:23	4,43%
Non productive	Socomo	_Ongeldig	_Ongeldig	2:23:34	0,12%
Non productive	Stock	_Ongeldig	_Ongeldig	23:05:17	1,19%
Non productive	Team Leader	_Ongeldig	_Ongeldig	26:38:54	1,37%
Non productive	Transit	_Ongeldig	_Ongeldig	49:39:42	2,55%
Non productive	Transit Controle	_Ongeldig	_Ongeldig	23:31:51	1,21%
Non productive	Uitverdeling	_Ongeldig	_Ongeldig	2:25:35	0,12%
Non productive	Wikkelen Traiteur	_Ongeldig	_Ongeldig	7:03:23	0,36%
Non productive	Wikkelen Vlees	_Ongeldig	_Ongeldig	12:59:25	0,67%
Non productive	Wikkelen Zuivel	_Ongeldig	_Ongeldig	30:22:31	1,56%
Non productive	Total			1127:37:23	57,91%
Total				1947:09:50	100,00%

Re: % Calculation over multiple dimension

sunny_talwar — Mon, 26 Mar 2018 16:47:29 GMT

Try this

=sum({$<[Jobcode]={"*"}-{"ILUNP"}>}

Aggr(max(if([Aisle Zone]='FORK' and [Jobcode]<>'IINCH' and [Jobcode]<>'DPURP' and [Action Code] <> 'PM' and [Action Code] <> 'ML' and [Action Code] <> 'MC'

,[Assignment Replenish Time]

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

),[Assignment Code]))

sum(TOTAL {$<[Jobcode]={"*"}-{"ILUNP"}>}

Aggr(max(if([Aisle Zone]='FORK' and [Jobcode]<>'IINCH' and [Jobcode]<>'DPURP' and [Action Code] <> 'PM' and [Action Code] <> 'ML' and [Action Code] <> 'MC'

,[Assignment Replenish Time]

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

),[Assignment Code]))

Re: % Calculation over multiple dimension

amber2000 — Thu, 29 Mar 2018 14:42:15 GMT

Thank you so much Sunny, I've made a few mistakes in the expression .

You are my Qlikview hero.

topic Re: % Calculation over multiple dimension in QlikView

% Calculation over multiple dimension

Re: % Calculation over multiple dimension

Re: % Calculation over multiple dimension