How to calculate accumalative percentage by dimension

[mzim]

hi experts,

Please advise on how to get % on the last column which refers to every day and not to total days i get wrong and i need the correct percentage which is produced manually.

Action.StartDate	HR	DTD Picked Order Lines	DiplAccumulated picked lines	wrong percentage	correct percentage
01-03-2023	9	124	124	6,04%	9,41%
01-03-2023	10	323	447	21,78%	33,92%
01-03-2023	11	112	559	27,24%	42,41%
01-03-2023	12	378	937	45,66%	71,09%
01-03-2023	13	244	1.181	57,55%	89,61%
01-03-2023	14	82	1.263	61,55%	95,83%
01-03-2023	15	55	1.318	64,23%	100,00%
01-03-2023	16	0	1.318	64,23%	100,00%
01-03-2023	17	0	1.318	64,23%	100,00%
01-03-2023	18	0	1.318	64,23%	100,00%
02-03-2023	9	177	177	8,63%	24,11%
02-03-2023	10	267	444	21,64%	60,49%
02-03-2023	11	167	611	29,78%	83,24%
02-03-2023	12	86	697	33,97%	94,96%
02-03-2023	13	32	729	35,53%	99,32%
02-03-2023	14	5	734	35,77%	100,00%
02-03-2023	15	0	734	35,77%	100,00%
02-03-2023	16	0	734	35,77%	100,00%
02-03-2023	17	0	734	35,77%	100,00%

Action.StartDate

HR=hour([Action.DateTime.Start])

DTD Picked Order Lines=Sum({<[Depositor.Code]={'001','003'},[OrderShipD.StatusCode]=-{'99'},[Action.ActionCode]={'70'}>}[OrderD.Records])

DiplAccumulated picked lines= RangeSum(Above(Sum({<[Depositor.Code]={'001','003'},[OrderShipD.StatusCode]=-{'99'},[Action.ActionCode]={'70'},[Action.ActionDescription]=>}[OrderD.Records]), 0, RowNo()))

wrong percentage= RangeSum(Above(Sum({<[Depositor.Code]={'001','003'},[OrderShipD.StatusCode]=-{'99'},[Action.ActionCode]={'70'},[Action.ActionDescription]=>}[OrderD.Records]), 0, RowNo())) /
Sum(total{<[Depositor.Code]={'001','003'},[OrderShipD.StatusCode]=-{'99'},[Action.ActionCode]={'70'},[Action.ActionDescription]=>}[OrderD.Records])

correct percentage = ??????????

Thanks in advance

[marksouzacosta]

Hi @mzim,

You have to fix a few things:

1. Replace [OrderShipD.StatusCode]=-{'99'} with [OrderShipD.StatusCode] -= {'99'}, meaning not equal 99

2. I believe you are missing an argument in yout Total Expression. Replace:
Sum(total{<[Depositor.Code]={'001','003'},[OrderShipD.StatusCode]=-{'99'},[Action.ActionCode]={'70'},[Action.ActionDescription]=>}[OrderD.Records])
With
Sum(total <Action.StartDate> {<[Depositor.Code]={'001','003'},[OrderShipD.StatusCode]=-{'99'},[Action.ActionCode]={'70'},[Action.ActionDescription]=>}[OrderD.Records])

3. I believe your Action.StartDate is a TimeStamp field. If so, you have to adjust this field to exclude the time part of it. Use this in your Load Script:
Date(Floor(Action.StartDate)) AS ActionStartDate

4. You can also add your HR field to the Load Script:
Hour([Action.DateTime.Start]) AS HR

Please let us know if that worked.

Regards,

Mark Costa

[mzim]

Hi  Mark,

I followed your steps one by one, but unfortunately, I got the same results.