Sum rows and split values into separate columns based on a threshold

jamjar

Hi everyone,

Hoping someone can help me with a challenge...

I've got daily quantity data (Monday - Sunday) that's grouped by a location key and then a sub-category. For example:

Key	Category	Mon	Tue	...
1	A	2	6
1	B	5	3
1	C	3	1
2	D	5	3
2	E	4	3
2	F	6	2
...

My goal is to identify when the total daily quantity for a given location key exceeds a threshold value. Then split the quantity into 'base' and 'extra' buckets.

For example, using the data above and a threshold of 8. For location key 1, the total across categories A, B and C for Monday is 10. So the base values would be A=2 (as 2 <=8), B=5, (as 2+5<=8) and C=1 (as this brings the base total to the threshold). The extra values would then be equal to [Mon] - [Mon_Base]. 

The example output would look like this: 

Key	Category	Mon	Tue	...	Mon_Base	Tue_Base	...	Mon_Extra	Tue_Extra	...
1	A	2	6		2	6		0	0
1	B	5	3		5	2		0	1
1	C	3	1		1	0		2	1
2	D	5	3		5	3		0	0
2	E	4	3		3	3		1	0
2	F	6	2		0	2		6	0
...

Thanks in advance!

Qiu

@jamjar 
Can you check if this works for you.

But there is a limitation that if the thread hold is quite large, and cannot accomendate by Base and Extra, then it will not work.

0714-jamjar.yxmd