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 · Accepted solution]

@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.

[jamjar]

Love your work!

One very small edit required to the multirow formula to account for situations where the first category for the location key was greater than the threshold. The bolded bit below was referencing the Row-1 ThreadHold (which for the 1st category/row was treated as 0 rather than 8)

Updated formula I used:

if [RunTot_Value]<= [ThreadHold] then [Value] else if [Row-1:ThreadHold]-[Row-1:RunTot_Value]>=0 then [ThreadHold]-[Row-1:RunTot_Value] else 0 endif endif