TM1 Forum

Hi,

I am trying to write a formula that will send my forecast units into the forecast cube (TM1 version 10.2.2).

Currently there is a document that has been set up with the following formula:
=DBRW("TM1ops:FC2","Plan","FY17","All FF Replenishment Regions","Feb","23486645","forecast Demand Units") [where 23486645 is the product code]
To amend the forecast units to 150, I currently have to type "d150" in the cell and this calculates the whole sheet, which is quite large (because I am forecasting several hundred items by month), so I'm constantly waiting for the sheet to recalculate.

In previous organisations, I have been able to use a formula that references another column (where my forecast data is) and send that to the database. I have used this in conjuction with an "if" statement that when I have a "Y" in a certain cell, the data is sent to the database. As a first step though, I need to get the TM1 formula to send the data to the database.

I have tried the following formula, but it isn't working for me - this is what I have tried:
=DBS(A6,"TM1ops:FC2","Plan","FY17","All FF Replenishment Regions","Feb","23486645","forecast Demand Units") [where A6 references the cell where I have calculated the forecast value]. I have also tried DBSS and DBRW with the same formula.

Is there anyone that can help me with this?

Thanks,



Lisa

ldoherty wrote:I have tried the following formula, but it isn't working for me - this is what I have tried:
=DBS(A6,"TM1ops:FC2","Plan","FY17","All FF Replenishment Regions","Feb","23486645","forecast Demand Units") [where A6 references the cell where I have calculated the forecast value]. I have also tried DBSS and DBRW with the same formula.

"All Replenishment Regions". This sounds like a parent that all the replenishment regions roll up to, correct? If so then you can't send data to it as the DBS formula can only send data to the replenishment regions individually. This is true for all the other dimensions as well.

ldoherty wrote: I have tried the following formula, but it isn't working for me

"Similarly if you're getting unexpected results, specifics of what you're running, how, and what results you're getting will yield a more valuable response than "... my code doesn't work properly". "

So what, exactly, isn't working for you? Is there an error showing in the cell? If so, what is it? What exactly does the cell show?

ldoherty wrote: I am trying to write a formula that will send my forecast units into the forecast cube (TM1 version 10.2.2).

Currently there is a document that has been set up with the following formula:
=DBRW("TM1ops:FC2","Plan","FY17","All FF Replenishment Regions","Feb","23486645","forecast Demand Units") [where 23486645 is the product code]
To amend the forecast units to 150, I currently have to type "d150" in the cell

Can you please clarify whether "forecast Demand Units" is an N element or an S element?

If it's an N element then as Tomok said you can't write a numeric value to a cell which is defined by consolidation elements. Also, "d150" is not a valid input for a numeric cell. You can enter some alphabetic characters in a numeric cell to trigger data spreading, but as far as I can recall (and as far as I can find in the documentation) d is not one of those codes.

If it's an S element, then you can enter a value to a combination that contains consolidations. But whether that gets you the results that you want will depend on what other rules or TI processes you have in place to support it.

ldoherty wrote:and this calculates the whole sheet, which is quite large (because I am forecasting several hundred items by month), so I'm constantly waiting for the sheet to recalculate.

Which is why your Excel recalculation mode (File => Options => Formulas) should be set to Manual rather than Automatic when working with TM1 sheets.

ldoherty wrote:In previous organisations, I have been able to use a formula that references another column (where my forecast data is) and send that to the database. I have used this in conjuction with an "if" statement that when I have a "Y" in a certain cell, the data is sent to the database. As a first step though, I need to get the TM1 formula to send the data to the database.

The usual cause of failure in cases like this is that an element in a DBRW (or similar) formula is derived from another DBRW formula. If you need to do a lookup of an element name from a TM1 cube, that lookup has to be a DBR rather than a DBRW.

ldoherty wrote:I have tried the following formula, but it isn't working for me - this is what I have tried:
=DBS(A6,"TM1ops:FC2","Plan","FY17","All FF Replenishment Regions","Feb","23486645","forecast Demand Units") [where A6 references the cell where I have calculated the forecast value]. I have also tried DBSS and DBRW with the same formula.

They aren't interchangeable. DBS will send a numeric value to a cube, but not a string. DBSS will send a string to a cube, but not a number. DBRW won't send a value in another cell, but will let you type a value straight into the cube, assuming that you have write access to that combination. If you're writing a numeric value rather than a string the elements all need to be N level as well.