Prop. Spread on calculated C-Element

Post Reply
TM1NB
Posts: 18
Joined: Thu Jan 02, 2020 3:05 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: Excel 365

Prop. Spread on calculated C-Element

Post by TM1NB »

Hey together,

actual having an issue with my rule / porportional data spread.

My example cube is simple, just 2D with 3 measures and a time-dimension with 12 month and a year element.
Screenshot.png
Screenshot.png (3.48 KiB) Viewed 1228 times
My rule:

Code: Select all

['REQ'] = C: (['UMS-RED-Betrag-vK'] - ['UMS-RED-Betrag-ez']) \ ['UMS-RED-Betrag-vK'];
['UMS-RED-Betrag-vK'] = N:['UMS-RED-Betrag-EZ'] \ (1-['REQ']);
Doing a prop. spread on tm1 "REQ" / "ZE-9999" will cause the following Error:
Spreading failed due to conflicts with rules.

I already tried to write the rule in different ways, but I couldn't solve it...

Do you guys have an idea how I have to change my rule to fix this and get the correct output?

Thanks!
Best regard

tm1nb
Mark2007
Posts: 45
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: PA 2
Excel Version: office 365

Re: Prop. Spread on calculated C-Element

Post by Mark2007 »

Did you find a solution as I'm facing the same problem?

I assume, that this "inverse spreading" is only possible for simple functions, such as:
Sales = Units * Price,
( A = B * C)
you can insert sales, price is kept stable an the system does the spread to units.

In your case, as
A = (B - C ) / B
TM1 is not able to do the spreading to B and keep C stable

Am I wrong?

Regards
Mark
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Prop. Spread on calculated C-Element

Post by Wim Gielis »

Mark2007 wrote: Tue Nov 22, 2022 5:34 pm Did you find a solution as I'm facing the same problem?

I assume, that this "inverse spreading" is only possible for simple functions, such as:
Sales = Units * Price,
( A = B * C)
you can insert sales, price is kept stable an the system does the spread to units.

In your case, as
A = (B - C ) / B
TM1 is not able to do the spreading to B and keep C stable

Am I wrong?

Regards
Mark
Hi, I haven't done a lot of such rules calculations.

But if you say that A = (B - C ) / B is too difficult, would it help if you write:
A = 1 - C / B
or even
A = 1 - C \ B

The outcome is different though. In your case, if B = 0 then A = 0 (if you use a backslash instead of forward slash for divisions.
In my case if B = 0 then A = 1.
So next to being possible or not, one should be careful too.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Mark2007
Posts: 45
Joined: Tue Jan 07, 2014 12:07 pm
OLAP Product: Cognos TM1
Version: PA 2
Excel Version: office 365

Re: Prop. Spread on calculated C-Element

Post by Mark2007 »

Hi Wim, thanks for your idea, but it does not work either :-(
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Prop. Spread on calculated C-Element

Post by Steve Rowe »

I don't think proportional spread against a calculated C level element will work, even when the Ns below it are open.

In theory there is nothing to stop the engine doing the work, perhaps worth raising with IBM to see if they thinks its a bug but this I would expect to raise an enhancement request for this. This will take time and probably won't go anywhere...

It's kind of ugly but I think you'll need another measure to calculate the average price for reporting and leave the input open versus the input price.
Technical Director
www.infocat.co.uk
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Prop. Spread on calculated C-Element

Post by MarenC »

Hi,

I obviously don't understand the real nature of this problem, but going back to the original query, if I proportionally spread to the right on REQ, ZE-9999 then it works ok.

If I leave the spread to the right box unchecked then it shows the conflict rules error.

Maren
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Prop. Spread on calculated C-Element

Post by lotsaram »

Spreading over a rule does work as long as at the leaf level there is only 1 independent variable. As you have more than one potential input to flex spreading can't be done.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Post Reply