Values Overwriting in Allocation

[Former Member]

Hi Vadim,

Please see the below profit center dimension members.

Here i want allocate all source cost centers to only single cost center in different profit center which is highlighted. While allocating the value should add.

Source	Target	Target	Target	Target	Target	Target
610601	610101	610201	610202	610301	610302	610401
6106010104	6101010001	6102010003	6102020003	6103010003	6103020103	6104010004
6106010109	6101010002	6102010004	6102020004	6103010004	6103020104	6104010005
6106010113	6101010003	6102010005	6102020005	6103010005	6103020105	6104010007
6106010119	6101010004	6102010007	6102020007	6103010007	6103020107	6104010008
6106010204	6101010005	6102010008	6102020008	6103010008	6103020108	6104010013
6106010209	6101010006	6102010009	6102020010	6103010010	6103020110	6104010016
6106010213	6101010007	6102010010	6102020011	6103010011	6103020111	6104010018
6106010219	6101010008	6102010011	6102020013	6103010013	6103020113	6104010020
6106010304	6101010033	6102010033	6102020033	6103010033	6103020233	6104010033
610501
6105010002
6105010003
6105010004
6105010005	6101010033	Target Cost center
6105010006
6105010007
6105010008
6105010009
6105010011
610102
6101020001
6101020002
6101020003
6101020004
6101020005
6101020006
6101020007
6101020008
6101020009

I want like this

				610601	610501	6E+05
	Cost Center	ACCOUNT	ABP	ALLOC1	ALLOC2	ALLOC3
610601	6106010104	x	500
	6106010109	x	1000
	6106010113	x	1500
		Allocshare
	6106010133
610501	6105010002	x	100
	6105010003	x	200
	6105010004	x	300
		Allocshare
	6105010033
610102	6101020001	x	600
	6101020002	x	700
	6101020003	x	800
		Allocshare
	6101020033
610101	6101010001	x
	6101010002	x
	6101010003	x
		Allocshare		10	10	10
	6101010033			300	60	210
610201	6102010003	x
	6102010004	x
	6102010005	x
		Allocshare		10	10	10
	6102010033			300	60	210
610202	6102020003	x
	6102020004	x
	6102020005	x
		Allocshare		20	20	20
	6102020033			600	120	420
610301	6103010003	x
	6103010004	x
	6103010005	x
		Allocshare		20	20	20
	6103010033			600	120	420
610302	6103020103	x
	6103020104	x
	6103020105	x
		Allocshare		20	20	20
	6103020233			600	120	420
610401	6104010004	x
	6104010005	x
	6104010007	x
		Allocshare		20	20	20
	6104010033			600	120	420

                                                                                                                                                                                                                                             Thanks

Gowtham                                                                                                                              

[former_member186338]

Sorry, but the first picture is absolutely not clear! And allocation will always overwrite, not add!

[Former Member]

Hi Vadim,

First picture is my profit center dimension members. Each profit center have cost centers. which is 6 digit number it is my profit center and which is 10 digits it is my cost centers. In each profit center i have one "allocate cost center"

6101010033

6102010033

6102020033

6103010033

6103020233

6104010033

i want to allocate different cost centers (Source) to the above cost centers only.

What i have to do for add the value. Like my second report.

Thanks

Gowtham

[former_member186338]

Still not clear, please use Excel screenshot with clear explanation!

[Former Member]

Hi Vadim,

Please see the below sreen.

[Former Member]

Hi,

The values should add to only one cost center only from all cost centers like above report.

6106010104	x	500
6106010109	x	1000
6106010113	x	1500

	Allocshare		10
6101010033			300

10% on 500,1000,1500= 300

Thanks

Gowtham

[former_member186338]

But what is the issue?

You have number of cost centers under some parent:

BAS(610601) = 6106010104,6106010109,6106010113

*DIM CC WHAT=BAS(610601)

Target cost centers:

%CCT%=6101010033,6102010033,...

WHERE=%CCT%

USING=%CCT%

TOTAL=%CCT%

For each parent like 610601 you need to have separate ALLOCx

[Former Member · Accepted solution]

Thanks Vadim,

It is working fine. But when i am doing one by one cost center the destination value is not adding. It is overwriting by next cost center value. Here destination is but source is different. In destination the value should add.

I am taking above example

But i want like below

Please help me on this

Thanks

Gowtham

[former_member186338 · Accepted solution]

Sorry, I have already told you that allocation never add, always overwrite!!!!

You can't do it "one by one cost center", have to allocate list!

[Former Member]

Thanks Vadim,

Sorry for the delay reply.

Is there any way to achieve this requirement. It is my client most requirement.

Thanks

Gowtham

[former_member186338]

But what is the issue with allocation of the list????

[Former Member]

Hi Vadim,

Allocation is working fine 100% and my client also satisfied.

But My client another requirement is :

He want to allocate some value from one cost center to "allocated" Cost center. This is my target cost center name.

The same way he want to allocate from other cost centers to  "allocated" Cost center.( Same Target )

While doing this the values should not overwrite. It should add every time in "allocated" Cost center.

Thanks

Gowtham

[former_member186338]

If you don't want to overwrite then you have to create a lot of members ALLOCx and allocate each source costcenter to individual ALLOCx. But I have explained this logic already number of times.

[Former Member]

Thanks Vadim,

I have created all the members to do allocation. It is working fine.

But here i am facing the problem in script. I don't want mention all the members in script which i have created. I want use any property for all the members.

Is there any solution for this.

Thanks

Gowtham

[former_member186338]

Sorry, but "want use any property for all the members" is not a description of your idea! Please provide full details.

[Former Member]

Hi Vadim,

*XDIM_MEMBERSET TS_CATEGORY= ABP

*XDIM_MEMBERSET TIME= 2017.01,2017.02,2017.03,2017.04

*SELECT(%ACT%,[ID],TS_ACCOUNT,[ID]=31300024,31300031,31300041,31300054,40090001,40090002,40110001,40110002,40110003,40110004,40110132,40110133,40110134,44000001,44000002,44000006,44000007,44000008,44000009,44000010,44000019,44000025,44000305,44000308,44000816,44000829,44000841,44001101,44002304,44002311,44002321,44002334,30200011,30200021,30900504,31200021,61000031,44000839,44000833,44001102,44000834,44000503,44000808,44001201,44000201,44000302,44000307,44000309,44000311,44000403,44001401,44002551,41000001,41000002,41000003,41000004,41000005,41000007,41000009,41000010,41000011,41000012,41000051,41000065,41000553,41000501,41000511,41000541,41000566,41000521,41000532,41000551,41000561,41000562,44000703,41000555,41000563,41000571,41000574,41000018,41000052,41000056,44000802,44000830,44000701,44000702,44000705,44000707,44000708,44000811,44000815,44000832,44000801,44000804,44002501,44000810,44000847,44002604,44000818,44000819,44000836,44002401,44002101,44002102,44002103,44002606,44000809,44000817,44000828,44000838,44000501,44000821,44000823,44000102,44000824,44000603,44001501,42000171,42000181,42000052,42000061,42000251)

*SELECT(%BES%,[ID],TS_PCTR,[ID]=6105010002,6105010025,)

*SELECT(%BET%,[ID],TS_PCTR,[ID]=6102010033,6102020033,6103020233)

*SELECT(%CAT%,[ID],TS_CATEGORY,[ID]=CORP_ALLOCATION)

*FOR %ALL%=%BES% AND %ALT%=%CAT%

*RUNALLOCATION

*FACTOR =USING/TOTAL

*DIM_NONAGGR TS_ACCOUNT WHAT=%ACT%; WHERE=<<<; USING=ALLOCSHARE; TOTAL=<<<

*DIM TS_PCTR WHAT=%ALL%; WHERE=%BET%; USING=<<<; TOTAL=<<<

*DIM TS_CATEGORY WHAT=ABP; WHERE=%ALT%; USING=<<<; TOTAL=<<<

*ENDALLOCATION

*NEXT

This is my script. It working fine. In this i don't want mention all the account members. Instead of all members. Can i use any property.

Even in category also i have many members to insert.

Thanks

Gowtham

[former_member186338]

For sure you can use the property instead of direct member list:

*SELECT(%ACT%,[ID],TS_ACCOUNT,[ID]=31300024,31300031,31300041,31300054,40090001,40090002,40110001,40110002,40110003,40110004,40110132,40110133,40110134,44000001,44000002,44000006,44000007,44000008,44000009,44000010,44000019,44000025,44000305,44000308,44000816,44000829,44000841,44001101,44002304,44002311,44002321,44002334,30200011,30200021,30900504,31200021,61000031,44000839,44000833,44001102,44000834,44000503,44000808,44001201,44000201,44000302,44000307,44000309,44000311,44000403,44001401,44002551,41000001,41000002,41000003,41000004,41000005,41000007,41000009,41000010,41000011,41000012,41000051,41000065,41000553,41000501,41000511,41000541,41000566,41000521,41000532,41000551,41000561,41000562,44000703,41000555,41000563,41000571,41000574,41000018,41000052,41000056,44000802,44000830,44000701,44000702,44000705,44000707,44000708,44000811,44000815,44000832,44000801,44000804,44002501,44000810,44000847,44002604,44000818,44000819,44000836,44002401,44002101,44002102,44002103,44002606,44000809,44000817,44000828,44000838,44000501,44000821,44000823,44000102,44000824,44000603,44001501,42000171,42000181,42000052,42000061,42000251)

*SELECT(%ACT%,[ID],TS_ACCOUNT,[SOMEPROPERTY]=SOMEVALUE)

What is the issue???

[Former Member]

Hi Vadim,

Here i am sending my dimension members.

And here my script:

*XDIM_MEMBERSET TIME= 2017.01,2017.02,2017.03,2017.04

*SELECT(%ACT%,[ID],TS_ACCOUNT,[ACT]=ACT)

*SELECT(%BES%,[ID],TS_PCTR,[ALL]=BES)

*SELECT(%BET%,[ID],TS_PCTR,[ALL]=BET)

*SELECT(%CAT%,[ID],TS_CATEGORY,[ALT]=ATT)

*FOR %ALL%=%BES% AND %ATT%=%CAT%

*RUNALLOCATION

*FACTOR =USING/TOTAL

*DIM_NONAGGR TS_ACCOUNT WHAT=BAS(P&L); WHERE=<<<; USING=ALLOCSHARE; TOTAL=<<<

*DIM TS_PCTR WHAT=%ALL%; WHERE=%BET%; USING=%ALL%; TOTAL=<<<

*DIM TS_CATEGORY WHAT=ABP; WHERE=%ATT%; USING=<<<; TOTAL=<<<

*ENDALLOCATION

*NEXT

Is it right

Thanks

Gowtham

[former_member186338]

It will not work:

*SELECT(%BES%,[ID],TS_PCTR,[ALL]=BES)

*SELECT(%CAT%,[ID],TS_CATEGORY,[ALT]=ATT)

will generate lists in the wrong sequence!

*FOR %ALL%=%BES% AND %ATT%=%CAT%

It's better to create additional property in TS_PCTR like CAT and fill it with the required TS_CATEGORY member!

Then:

*SELECT(%CAT%,[CAT],TS_PCTR,[ALL]=BES)

[Former Member]

Hi Vadim,

If i added prooerty in TS_PCTR as CAT and maintained TS_CATEGORY members as CAT property value than the script is giving error like

What i have mentioned in TS_PCTR CAT property values are does not exist.

RUN_LOGIC:Member "MOD_P" not exist

Can you give any suggestion.

Thanks

Gowtham

[former_member186338]

It will work! Please provide all details (screenshots, code etc...)... unable to help with just "RUN_LOGIC:Member "MOD_P" not exist"

[Former Member]

Hi Vadim,

Here my dimensions screen shot.

And here my script:

*XDIM_MEMBERSET TIME= %TIME_SET%

*SELECT(%ACT%,[ID],TS_ACCOUNT,[ACT]=ACT)

*SELECT(%BES%,[ID],TS_PCTR,[ALL]=BES)

*SELECT(%BET%,[ID],TS_PCTR,[ALL]=BET)

*SELECT(%CAT%,[CAT],TS_PCTR,[ALL]=BES)

*SELECT (%YR1%,[YEAR],TIME,ID=%TIME_SET%)

*FOR %ALL%=%BES% AND %ATT%=%CAT%

*RUNALLOCATION

*FACTOR =USING/TOTAL

*DIM_NONAGGR TS_ACCOUNT WHAT=BAS(P&L); WHERE=<<<; USING=ALLOCSHARE; TOTAL=<<<

*DIM TS_PCTR WHAT=%BES%; WHERE=%BET%; USING=%ALL%; TOTAL=<<<

*DIM TS_CATEGORY WHAT=ABP; WHERE=%ATT%; USING=<<<; TOTAL=<<<

*ENDALLOCATION

*NEXT

@ If possible can you connect through teamviewer

Thanks

Gowtham

[former_member186338]

But where is the screenshot of your TS_CATEGORY dimension?

In CAT property you need to specify members of TS_CATEGORY dimension!

"If possible can you connect through teamviewer" - sorry, but it will be not free service!

[Former Member]

Hi Vadim,

2nd screen shot is the TS_CATEGORY dimension.

TS_CATEGORY dimension members are maintain in TS_PCTR dimension (1st Screen shot) in CAT property as a property value.

Can you provide your mail ID. Will talk separately.

Thanks

Gowtham

[former_member186338]

In CAT property I see values like MOD_xx but in TS_CATEGORY I see only members like CORP_xx

What do you mean by "MOD" -???

P.S. Try Skype vadim.e.kalinin

[Former Member]

Hi Vadim,

There are many members in TS_CATEGORY dimension. While i am taking screen shot all members are not coming.

MOD_xx is my cost center desc in TS_PCTR dimension. So I have taken this in to category for allocation purpose.

Thanks

Gowtham

[former_member186338]

"MOD_xx is my cost center desc" ????

In CAT property you need to have valid TS_CATEGORY Member, not "desc"....

[Former Member]

Hi Vadim,

I am taking a valid TS_CATEGORY members only as CAT property values.

See below screen shots.

TS_PCTR

TS_CATEGORY

For each source cost center i have created category member for allocation purpose.

Thanks

Gowtham

[former_member186338]

OK, but the script contains errors, look on the correct:

*XDIM_MEMBERSET TIME= %TIME_SET%

*SELECT(%ACT%,[ID],TS_ACCOUNT,[ACT]=ACT)

*SELECT(%BES%,[ID],TS_PCTR,[ALL]=BES)

*SELECT(%BET%,[ID],TS_PCTR,[ALL]=BET)

*SELECT(%CAT%,[CAT],TS_PCTR,[ALL]=BES)

*SELECT (%YR1%,[YEAR],TIME,ID=%TIME_SET%)

*FOR %ALL%=%BES% AND %ATT%=%CAT%

*RUNALLOCATION

*FACTOR =USING/TOTAL

*DIM_NONAGGR TS_ACCOUNT WHAT=BAS(P&L); WHERE=<<<; USING=ALLOCSHARE; TOTAL=<<<

*DIM TS_PCTR WHAT=%ALL%; WHERE=%BET%; USING=%BET%; TOTAL=<<<

*DIM TS_CATEGORY WHAT=ABP; WHERE=%ATT%; USING=<<<; TOTAL=<<<

*ENDALLOCATION

*NEXT

[Former Member]

Thanks Vadim,

Thanks for your valuable time and inputs and guidance provided for our issue. Will be always honor to you.

Thanks

Gowtham