Unable to add the difference in the foreach.booked with each product in SAP SAC Data action

[vasapuramsuryaharshasimha]

Hi All,

Here is the scenario we are facing the issues, we have to read each record by record and calculate the sub total and main total also should remain same even after re-allocation at discrete level.

Measure used is a Volume ,it cannot be a decimal.

we have some pre-populated volumes from source for PNO12 with sub -totals as 45, 9 and 27 respectively.

Now user ,will re-allocate the volumes from existing customer category to a new categories like 

10A = 30 , 20A = 30 and 71A = 21, after the allocation sub totals should remain same at CC and PNO12

like 45 at 224 H1 G3 0 D 1 19 and so on.

Please correct this below code and suggest if any idea and it should be dynamic.

CONFIG.HIERARCHY.INCLUDE_MEMBERS_NOT_IN_HIERARCHY =[d/DIM_BPACCT]

CONFIG.READ_CALCULATED_MEMBER_VALUES =ON

MEMBERSET [d/Measures] ="ZVOLUME"

MEMBERSET [d/DIM_DATASRC]="CALCULATE"

MEMBERSET [d/DIM_MODELYR]="#"

MEMBERSET [d/DIM_FACTORY]="#"

MEMBERSET [d/Date] = [d/Version].[p/Cutoff_Date] TO [d/Version].[p/Enddate]

MEMBERSET [d/DIM_MKT] = BASEMEMBER([d/DIM_MKT] , %UNMarket%)

MEMBERSET [d/DIM_PNO3]= BASEMEMBER([d/DIM_PNO3] ,%PNO3% )

MEMBERSET [d/DIM_VERSION]=%PlanningVersion%

//MEMBERSET [d/DIM_PNO12] =("224 H1 G3 0 D 1 19", "224 H4 G3 0 D 1 19")

VARIABLEMEMBER #Total_Amount OF  [d/Measures]  

VARIABLEMEMBER #STORE6520_COPY OF [d/Measures]

VARIABLEMEMBER #Total6520_INPUT OF [d/Measures]  

VARIABLEMEMBER #CCPercentage OF [d/Measures] 

VARIABLEMEMBER #Store_6520INPUT OF [d/Measures] 

VARIABLEMEMBER #Total_6520 OF [d/DIM_BPACCT]  

VARIABLEMEMBER #Subtotal_of_Each_CC OF [d/DIM_PNO12]

VARIABLEMEMBER #Difference_in_CC OF [d/Measures] 

VARIABLEMEMBER #PNO12SUBTOTAL_POSTALLOCATION OF  [d/DIM_CUSTCAT]

VARIABLEMEMBER #TEST1 OF [d/Measures]

VARIABLEMEMBER #TEST2 OF [d/Measures]

VARIABLEMEMBER #TEST3 OF [d/Measures]

VARIABLEMEMBER #TEST4 OF [d/Measures]

VARIABLEMEMBER #Store OF [d/Measures] 

VARIABLEMEMBER #Store2 OF [d/Measures] 

VARIABLEMEMBER #TOTALOFPREVIOUS OF [d/Measures]

VARIABLEMEMBER #Account OF [d/DIM_BPACCT]  

VARIABLEMEMBER #CC OF [d/DIM_CUSTCAT]

VARIABLEMEMBER #SubTotatofPNO12 OF [d/DIM_CUSTCAT]

VARIABLEMEMBER #CCtotal OF [d/DIM_CUSTCAT] 

VARIABLEMEMBER #FINDDIFF OF  [d/Measures]

VARIABLEMEMBER #VOLSTORE OF [d/Measures] 

VARIABLEMEMBER #FINALSTORE OF [d/Measures] 

INTEGER @count

INTEGER @CurrentCount

INTEGER @GETTOTAL

//Simple copy from 6520_COPY to store it in Variable member

DATA( [d/DIM_BPACCT]="6520_COPY", [d/Measures]=#STORE6520_COPY ) = RESULTLOOKUP([d/DIM_BPACCT]="6520_COPY", [d/Measures]="ZVOLUME")

//Store User Input values 6520_INPUT Example: 10A-27 and 20A-27

DATA([d/Measures]=#Store_6520INPUT, [d/DIM_BPACCT]="6520_INPUT" ) =RESULTLOOKUP([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_INPUT")

//Subtotal of Each PNO12 Example: 45 , 9 (Account: 6520_COPY)

DATA( [d/DIM_BPACCT]="6520_COPY", [d/Measures]=#Total_Amount, [d/DIM_CUSTCAT]=#SubTotatofPNO12 ) = RESULTLOOKUP([d/DIM_BPACCT]="6520_COPY", [d/Measures]="ZVOLUME")

//Store user inputted values example, 27+27 = 54 will get here (Account: 6520_INPUT)

DATA([d/Measures]=#Total6520_INPUT, [d/DIM_CUSTCAT]=#CCtotal,[d/DIM_BPACCT]="6520_INPUT", [d/DIM_ENGINE]="#",[d/DIM_FACTORY]="#", [d/DIM_INDICATOR]="#", [d/DIM_MODELYR]="#", [d/DIM_PNO12]="#", [d/DIM_PRD_ID]="#", [d/DIM_SV]="#" )

         = RESULTLOOKUP([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_INPUT", [d/DIM_ENGINE]="#",[d/DIM_FACTORY]="#", [d/DIM_INDICATOR]="#", [d/DIM_MODELYR]="#", [d/DIM_PNO12]="#", [d/DIM_PRD_ID]="#", [d/DIM_SV]="#")

//Find the Percentage for user Inputted values Example: 27/54, 27/54 (Account: 6520_INPUT)  

DATA([d/Measures]=#CCPercentage,[d/DIM_BPACCT]="6520_INPUT", [d/DIM_ENGINE]="#",[d/DIM_FACTORY]="#", [d/DIM_INDICATOR]="#", [d/DIM_MODELYR]="#", [d/DIM_PNO12]="#", [d/DIM_PRD_ID]="#", [d/DIM_SV]="#" ) =( RESULTLOOKUP([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_INPUT", [d/DIM_ENGINE]="#",[d/DIM_FACTORY]="#", [d/DIM_INDICATOR]="#", [d/DIM_MODELYR]="#", [d/DIM_PNO12]="#", [d/DIM_PRD_ID]="#", [d/DIM_SV]="#")/RESULTLOOKUP([d/Measures]=#Total6520_INPUT, [d/DIM_CUSTCAT]=#CCtotal,[d/DIM_BPACCT]="6520_INPUT", [d/DIM_ENGINE]="#",[d/DIM_FACTORY]="#", [d/DIM_INDICATOR]="#", [d/DIM_MODELYR]="#", [d/DIM_PNO12]="#", [d/DIM_PRD_ID]="#", [d/DIM_SV]="#" ))

//Multiply Percentage with PNO12 Subtotal Example: 0.5*45, 0.5*45  AND 0.5*9 , 0.5*9  (Account: 6520_COPY)//

//Volume will be 22, 22 AND 4, 4 due to integer measure

DATA([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#") = RESULTLOOKUP([d/Measures]=#CCPercentage,[d/DIM_BPACCT]="6520_INPUT", [d/DIM_ENGINE]="#",[d/DIM_FACTORY]="#", [d/DIM_INDICATOR]="#", [d/DIM_MODELYR]="#", [d/DIM_PNO12]="#", [d/DIM_PRD_ID]="#", [d/DIM_SV]="#" )*RESULTLOOKUP([d/DIM_BPACCT]="6520_COPY", [d/Measures]=#Total_Amount, [d/DIM_CUSTCAT]=#SubTotatofPNO12)

//Store the same calculation into variable member to get the decimal values (Original allocated values,Example: 22.5, 22.5 AND 4.5, 4.5)

DATA([d/Measures]=#VOLSTORE, [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#") = RESULTLOOKUP([d/Measures]=#CCPercentage,[d/DIM_BPACCT]="6520_INPUT", [d/DIM_ENGINE]="#",[d/DIM_FACTORY]="#", [d/DIM_INDICATOR]="#", [d/DIM_MODELYR]="#", [d/DIM_PNO12]="#", [d/DIM_PRD_ID]="#", [d/DIM_SV]="#" )*RESULTLOOKUP([d/DIM_BPACCT]="6520_COPY", [d/Measures]=#Total_Amount, [d/DIM_CUSTCAT]=#SubTotatofPNO12)

//PNO12 Subtotal after allocation Example: After allocation 22+22-->44 and  4+4-->8 

DATA([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_CUSTCAT]=#PNO12SUBTOTAL_POSTALLOCATION) =RESULTLOOKUP([d/DIM_BPACCT]="6520_COPY", [d/Measures]="ZVOLUME",[d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#")

//Each CC subtotal after allocation, Example : 10A=22+4->26 , 20A=22+4-->26

DATA([d/Measures]="ZVOLUME", [d/DIM_PNO12]=#Subtotal_of_Each_CC, [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#") = RESULTLOOKUP([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#")

//Find difference between Pre Allocated and Post Allocated... 10A(PreAlloc) - 10A(PostAlloc)

DATA([d/Measures]=#Difference_in_CC, [d/DIM_BPACCT]="6520_COPY" , [d/DIM_PNO12]="#", [d/DIM_MODELYR]="#", [d/DIM_FACTORY]="#") = RESULTLOOKUP([d/Measures]=#Store_6520INPUT, [d/DIM_BPACCT]="6520_INPUT",[d/DIM_PNO12]="#", [d/DIM_MODELYR]="#",[d/DIM_FACTORY]="#")- RESULTLOOKUP([d/Measures]="ZVOLUME", [d/DIM_PNO12]=#Subtotal_of_Each_CC, [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#")

//1st PNO12

IF [d/DIM_PNO12]!="#" THEN

FOREACH.BOOKED [d/DIM_PNO12]

@count=@Count+1

IF @count=1 THEN

IF RESULTLOOKUP( [d/DIM_BPACCT]="6520_COPY", [d/Measures]=#Total_Amount, [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_CUSTCAT]=#SubTotatofPNO12)!= RESULTLOOKUP([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_CUSTCAT]=#PNO12SUBTOTAL_POSTALLOCATION) THEN

IF [d/DIM_CUSTCAT]=%CustomerCategory% THEN

IF RESULTLOOKUP([d/Measures]="ZVOLUME", [d/DIM_PNO12]=#Subtotal_of_Each_CC, [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#")!= RESULTLOOKUP([d/Measures]=#Store_6520INPUT, [d/DIM_BPACCT]="6520_INPUT",[d/DIM_PNO12]="#", [d/DIM_MODELYR]="#",[d/DIM_FACTORY]="#")  THEN

DATA([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#")= RESULTLOOKUP([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#")+ RESULTLOOKUP([d/Measures]=#Difference_in_CC, [d/DIM_BPACCT]="6520_COPY" , [d/DIM_PNO12]="#", [d/DIM_MODELYR]="#", [d/DIM_FACTORY]="#" )

ENDIF

ENDIF

ENDIF

ENDIF

ENDFOR

ENDIF

//2nd PNO12

DATA([d/Measures]=#TEST1, [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_PNO12]="#")= RESULTLOOKUP([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_CUSTCAT]=[d/DIM_CUSTCAT].[p/ID])

DATA([d/Measures]=#TEST2, [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_CUSTCAT]=#CC)= RESULTLOOKUP([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_CUSTCAT]=[d/DIM_CUSTCAT].[p/ID])

IF [d/DIM_PNO12]!="#" THEN

FOREACH.BOOKED [d/DIM_PNO12]

@count=@Count+1

//FOREACH.BOOKED [d/DIM_CUSTCAT]

IF @count=2 THEN

IF [d/DIM_CUSTCAT]=%CustomerCategory% THEN

IF RESULTLOOKUP( [d/DIM_BPACCT]="6520_COPY", [d/Measures]=#Total_Amount, [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#",[d/DIM_CUSTCAT]=#SubTotatofPNO12)!= RESULTLOOKUP([d/Measures]=#TEST2, [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_CUSTCAT]=#CC) THEN

IF RESULTLOOKUP([d/Measures]=#TEST1, [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_PNO12]="#", [d/DIM_CUSTCAT]=[d/DIM_CUSTCAT].[p/ID])

!= RESULTLOOKUP([d/Measures]=#Store_6520INPUT, [d/DIM_BPACCT]="6520_INPUT", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_PNO12]="#",[d/DIM_CUSTCAT]=[d/DIM_CUSTCAT].[p/ID]) THEN

DATA([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#")= RESULTLOOKUP([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#")+ RESULTLOOKUP([d/Measures]=#Difference_in_CC, [d/DIM_BPACCT]="6520_COPY" , [d/DIM_PNO12]="#", [d/DIM_MODELYR]="#", [d/DIM_FACTORY]="#" )

ENDIF

ENDIF

ENDIF

ENDIF

//ENDFOR

ENDFOR

ENDIF

//3rd PNO12

DATA([d/Measures]=#TEST3, [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_PNO12]="#")= RESULTLOOKUP([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_CUSTCAT]=[d/DIM_CUSTCAT].[p/ID])

DATA([d/Measures]=#TEST4, [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_CUSTCAT]=#CC)= RESULTLOOKUP([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_CUSTCAT]=[d/DIM_CUSTCAT].[p/ID])

IF [d/DIM_PNO12]!="#" THEN

FOREACH.BOOKED [d/DIM_PNO12]

@count=@Count+1

//FOREACH.BOOKED [d/DIM_CUSTCAT]

IF @count=3 THEN

IF [d/DIM_CUSTCAT]=%CustomerCategory% THEN

IF RESULTLOOKUP( [d/DIM_BPACCT]="6520_COPY", [d/Measures]=#Total_Amount, [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#",[d/DIM_CUSTCAT]=#SubTotatofPNO12)!= RESULTLOOKUP([d/Measures]=#TEST4, [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_CUSTCAT]=#CC) THEN

IF RESULTLOOKUP([d/Measures]=#TEST3, [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_PNO12]="#", [d/DIM_CUSTCAT]=[d/DIM_CUSTCAT].[p/ID])

!= RESULTLOOKUP([d/Measures]=#Store_6520INPUT, [d/DIM_BPACCT]="6520_INPUT", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#", [d/DIM_PNO12]="#",[d/DIM_CUSTCAT]=[d/DIM_CUSTCAT].[p/ID]) THEN

DATA([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#")= RESULTLOOKUP([d/Measures]="ZVOLUME", [d/DIM_BPACCT]="6520_COPY", [d/DIM_FACTORY]="#", [d/DIM_MODELYR]="#")+ RESULTLOOKUP([d/Measures]=#Difference_in_CC, [d/DIM_BPACCT]="6520_COPY" , [d/DIM_PNO12]="#", [d/DIM_MODELYR]="#", [d/DIM_FACTORY]="#" )

ENDIF

ENDIF

ENDIF

ENDIF

ENDFOR

//ENDFOR

ENDIF

Data action-advanced formula.txt

[William_Yu1]

Not really sure about the requirement, how is the user input customer category level 30, 30, 21, 0, 0 results in 9. 10, 8, 0, 0

[vasapuramsuryaharshasimha]

Hi Yu, 

existing data from source :

UN Market	PNO12	Customer Category		Total Volumes Before Allocation
224	224 H1 G3 0 D 1 19	Totals	–	45
224	224 H1 G3 0 D 1 19	10A - Private sold	–	9
224	224 H1 G3 0 D 1 19	20A - Demonstrator	–	9
224	224 H1 G3 0 D 1 19	71A - Company cars	–	9
224	224 H1 G3 0 D 1 19	31A - Showroom	–	9
224	224 H1 G3 0 D 1 19	69A - Daily Rental by NSC	–	9
224	224 H4 G3 0 D 1 19	Totals	–	9
224	224 H4 G3 0 D 1 19	10A - Private sold	–	0
224	224 H4 G3 0 D 1 19	20A - Demonstrator	–	2
224	224 H4 G3 0 D 1 19	71A - Company cars	–	3
224	224 H4 G3 0 D 1 19	31A - Showroom	–	2
224	224 H4 G3 0 D 1 19	69A - Daily Rental by NSC	–	2
224	224 H4 V5 0 D 1 19	Totals	–	27
224	224 H4 V5 0 D 1 19	10A - Private sold	–	4
224	224 H4 V5 0 D 1 19	20A - Demonstrator	–	6
224	224 H4 V5 0 D 1 19	71A - Company cars	–	3
224	224 H4 V5 0 D 1 19	31A - Showroom	–	6
224	224 H4 V5 0 D 1 19	69A - Daily Rental by NSC	–	8
				81

User input : 

User Input at customer category level
UN Market
224	10A	30
224	20A	30
224	71A	21
224	31A	0
224	69A	0

UN Market	PNO12	Customer Category		Final output expected
224	224 H1 G3 0 D 1 19	Totals	–	45
224	224 H1 G3 0 D 1 19	10A - Private sold	–	18
224	224 H1 G3 0 D 1 19	20A - Demonstrator	–	16
224	224 H1 G3 0 D 1 19	71A - Company cars	–	11
224	224 H1 G3 0 D 1 19	31A - Showroom	–	0
224	224 H1 G3 0 D 1 19	69A - Daily Rental by NSC	–	0
224	224 H4 G3 0 D 1 19	Totals	–	9
224	224 H4 G3 0 D 1 19	10A - Private sold	–	3
224	224 H4 G3 0 D 1 19	20A - Demonstrator	–	4
224	224 H4 G3 0 D 1 19	71A - Company cars	–	2
224	224 H4 G3 0 D 1 19	31A - Showroom	–	0
224	224 H4 G3 0 D 1 19	69A - Daily Rental by NSC	–	0
224	224 H4 V5 0 D 1 19	Totals	–	27
224	224 H4 V5 0 D 1 19	10A - Private sold	–	9
224	224 H4 V5 0 D 1 19	20A - Demonstrator	–	10
224	224 H4 V5 0 D 1 19	71A - Company cars	–	8
224	224 H4 V5 0 D 1 19	31A - Showroom	–	0
224	224 H4 V5 0 D 1 19	69A - Daily Rental by NSC	–	0
				81

Sub total and PNO12 total should remain same , even after re-allocation to different customer categories.

[vasapuramsuryaharshasimha]

Our major concern here is we are able to achieve this with hardcoding the dimenion members within the core logic, but we are not able to make it dynamic, we cannot hard code values for 120000 products in the system

[vasapuramsuryaharshasimha]

Wonderful William - great complexity solved within a click -

[William_Yu1 · Accepted solution]

I assume the calculation can be a bit simpler with below calculation, rough idea:

1. Calculate initial re-allocation by ratio, as the measure is integer, decimals are trimmed, the total value in integer must be smaller than original(best case no difference).
2. Re-calculate the subtotal based on two dimension
3. Loop both dimensions to fill the gap, 1 per time.

//-----------------------------------------------------------------------------------
//Set Configurations Definitions for the Advanced Formulas.
//-----------------------------------------------------------------------------------
CONFIG.TIME_HIERARCHY = FISCALYEAR
CONFIG.GENERATE_UNBOOKED_DATA = OFF
CONFIG.HIERARCHY.INCLUDE_MEMBERS_NOT_IN_HIERARCHY = [d/Region]
//-----------------------------------------------------------------------------------
//The data region against which the formulas will be executed.
//-----------------------------------------------------------------------------------
MEMBERSET [d/Measures] = "VOL"
MEMBERSET [d/Date] = "202610"
MEMBERSET [d/Region] != "#"
MEMBERSET [d/Product_Group] != "#"
//-----------------------------------------------------------------------------------
//Calculation and Data Writing
//-----------------------------------------------------------------------------------
VARIABLEMEMBER #PR OF [d/Product_Group]
VARIABLEMEMBER #TOTAL OF [d/Product_Group]
VARIABLEMEMBER #REGION OF [d/Region]
VARIABLEMEMBER #PR_2 OF [d/Product_Group]
INTEGER @GAP

// Read subtotal by Region
DATA([d/Product_Group] = #PR) = RESULTLOOKUP()
// Read total of target 
DATA([d/Product_Group] = #TOTAL, [d/Region] = "#") = RESULTLOOKUP([d/Region] = "#")

// Calculate target by ratio 
DATA() = RESULTLOOKUP([d/Product_Group] = #PR) *
RESULTLOOKUP([d/Region] = "#") / RESULTLOOKUP([d/Product_Group] = #TOTAL, [d/Region] = "#")

// Check if there is gap
DATA([d/Region] = #REGION, [d/Product_Group] = #TOTAL) = RESULTLOOKUP()
@GAP = RESULTLOOKUP([d/Product_Group] = #TOTAL, [d/Region] = "#") - RESULTLOOKUP([d/Product_Group] = #TOTAL, [d/Region] = #REGION)
IF @GAP > 0 THEN
// Get new subtotal by product and region 
	DATA([d/Product_Group] = #PR_2) = RESULTLOOKUP()
	DATA([d/Region] = #REGION) = RESULTLOOKUP()
	FOREACH [d/Product_Group] ASC
		IF RESULTLOOKUP([d/Region] = "#") > RESULTLOOKUP([d/Region] = #REGION) THEN
			FOREACH [d/Region] ASC
				IF RESULTLOOKUP([d/Product_Group] = #PR) > RESULTLOOKUP([d/Product_Group] = #PR_2) AND
					RESULTLOOKUP([d/Region] = "#") > RESULTLOOKUP([d/Region] = #REGION) THEN
					IF RESULTLOOKUP() > 0 THEN
						DATA() = RESULTLOOKUP() + 1
						@GAP = @GAP - 1
						DATA([d/Product_Group] = #PR_2) = RESULTLOOKUP([d/Product_Group] = #PR_2) + 1
						DATA([d/Region] = #REGION) = RESULTLOOKUP([d/Region] = #REGION) + 1
					ENDIF
				ELSEIF RESULTLOOKUP([d/Product_Group] = #PR) > RESULTLOOKUP([d/Product_Group] = #PR_2) THEN
					BREAK
				ENDIF
			ENDFOR
		ELSE
			IF @GAP = 0 THEN
				BREAK
			ENDIF
		ENDIF
	ENDFOR
ENDIF
​