Using "IF" function with DBR/S formula

[reynoldscm]

I have a spreadsheet where I want to check a text value for each column. If the value = "Actuals" then I want to read the value from a cube, if the value = "Forecast" then I want to type a value into the cell and send it to the cube.

It appears that if I have a formula that begins with =DBR or =DBS then I can type in a value and the underlying formula remains in place. However, if I use the =IF at the beginning of the formula and then type in a value, the value is stored in the cell and the formula overwritten.

Any suggestions for how to test for a value and then do either a DBR or DBS and maintain the underlying formula?

Using TM1 Version 9.5.2

Thank you.

[Alan Kirk]

I have a spreadsheet where I want to check a text value for each column. If the value = "Actuals" then I want to read the value from a cube, if the value = "Forecast" then I want to type a value into the cell and send it to the cube.

It appears that if I have a formula that begins with =DBR or =DBS then I can type in a value and the underlying formula remains in place. However, if I use the =IF at the beginning of the formula and then type in a value, the value is stored in the cell and the formula overwritten.

Any suggestions for how to test for a value and then do either a DBR or DBS and maintain the underlying formula?

Using TM1 Version 9.5.2

As you've discovered DBR formulas are two way. If you have a DBR formula you can type a number straight into it to send it to the cube. Using a DBS is therefore unnecessary (though sometimes wise depending on whether or not users can be trusted not to obliterate the formulas by using the Delete key). If you therefore:
- Had DBRs in all of the cells;
- Had the DBR formulas pointing to the column header to determine which version to read from / write to; and
- Used element security to prevent people from writing to the Actuals version,
Then you wouldn't need to worry about If() functions.

The second thing is... don't use DBR formulas. Use DBRW ones. This will improve performance markedly.

[reynoldscm]

Alan...you got me thinking and I was able to do the IF() elsewhere on the sheet and just use the DBRW formula. Thanks for jogging my brain a bit.

[mattgoff]

Alan...you got me thinking and I was able to do the IF() elsewhere on the sheet and just use the DBRW formula. Thanks for jogging my brain a bit.

The real takeaway from Alan's reply is that you shouldn't be doing this in Excel at all. Unless there are unusual circumstances, actual vs forecast is best handled directly in TM1. It sounds like you're new to TM1 administration (so it's understandable that you might have a bias to do as much as possible in Excel), but the effort you put into learning how to do it in TM1 is well worth your time.

Matt

[Alan Kirk]

Alan...you got me thinking and I was able to do the IF() elsewhere on the sheet and just use the DBRW formula. Thanks for jogging my brain a bit.

The real takeaway from Alan's reply is that you shouldn't be doing this in Excel at all. Unless there are unusual circumstances, actual vs forecast is best handled directly in TM1.

I agree with you on that principle, calculations are better done on the server side if possible... but I wasn't reading the question as describing an actuals vs forecast sheet but rather one which displays actuals for the completed period in the left hand columns, and allows the input of forecast values for current and future periods. In other words, a forecast update input sheet rather than a report. The original post didn't make any mention of comparing the two on the worksheet.

[mattgoff]

I agree with you on that principle, calculations are better done on the server side if possible... but I wasn't reading the question as describing an actuals vs forecast sheet but rather one which displays actuals for the completed period in the left hand columns, and allows the input of forecast values for current and future periods. In other words, a forecast update input sheet rather than a report. The original post didn't make any mention of comparing the two on the worksheet.

Sorry, my "vs" was unclear-- I meant at the cell level, meaning one vs the other is shown. I agree that the OP probably wants a single sheet that shows Jan-Dec and as periods close the forecast value is replaced with the actual value.

BTW, your recommendation (and it's a popular one from what I've seen here) is a PITA (and also requires cell security) if you have separate period and year dims (as we do). In this situation, I think it's better to handle it with an Actuals version that's ruled into the Forecast version based on a value in a control cube. This obv has a real-time performance implication, but it's at least partially offset by the elimination of cell security. If you have a single time dim, your way works nicely.

Matt