Cells not calculating unless edited

Post Reply
NPTM1
Posts: 7
Joined: Mon Sep 26, 2016 9:59 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2010

Cells not calculating unless edited

Post by NPTM1 » Tue Nov 21, 2017 12:38 pm

Hi all

I'm not sure if this has come up before, but I've got a strange situation with a slice. I've got some DBRW cell forumulae which update ok, however SOME dependant cells don't seem to want to recalculate (e.g. a Sum formulae).

I've tried using Shift+F9, F9 and Alt+F9 but these dependant formulae don't seem to want to change. The only way I can get them to update is by selecting the cell and hitting F2 then Enter.

This isn't an overly complex workbook, and strangely other sheets work fine, it's just some formulae on this one tab that are playing up.

Has anyone come across this before or know a better way of recalculating the whole workbook?

Thanks in advance.

Mark RMBC
Posts: 86
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Cells not calculating unless edited

Post by Mark RMBC » Tue Nov 21, 2017 1:48 pm

Stab in the dark, but in the cells for the sum formulae, could they be formatted to text?

Have you tried inserting another column, ensuring the format is general and recreating the sum formula and testing that?

NPTM1
Posts: 7
Joined: Mon Sep 26, 2016 9:59 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2010

Re: Cells not calculating unless edited

Post by NPTM1 » Tue Nov 21, 2017 2:08 pm

They seems to work as normal after edit/enter. It was only 4 cells that were affected on the sheet. I guess I was hoping to find out the cause to avoid having this happen in future.

I know that I grouped tabs together to do a bulk edit, and maybe this triggered a bug. It's possible this was an excel issue and not a perspectives one.

User avatar
tomok
MVP
Posts: 2356
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Cells not calculating unless edited

Post by tomok » Tue Nov 21, 2017 6:06 pm

NPTM1 wrote:
Tue Nov 21, 2017 12:38 pm
I've got some DBRW cell forumulae which update ok, however SOME dependant cells don't seem to want to recalculate (e.g. a Sum formulae).
What exactly do you mean by "SOME dependant cells"? Do you have nested DBRW formulas. By that I mean the result of one DBRW is used in another DBRW? If that is the case it is a no-no. You will need to change the source DBRW to a DBR.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

tm123
Posts: 59
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Cells not calculating unless edited

Post by tm123 » Tue Nov 21, 2017 8:00 pm

Is this behavior in TM1Web or Perspectives?

Also, are they ruled cells, excel formulas, or TM1 Consolidations?

User avatar
Alan Kirk
Site Admin
Posts: 5729
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Cells not calculating unless edited

Post by Alan Kirk » Tue Nov 21, 2017 8:21 pm

NPTM1 wrote:
Tue Nov 21, 2017 2:08 pm
They seems to work as normal after edit/enter. It was only 4 cells that were affected on the sheet. I guess I was hoping to find out the cause to avoid having this happen in future.

I know that I grouped tabs together to do a bulk edit, and maybe this triggered a bug. It's possible this was an excel issue and not a perspectives one.
If you ever get this again, try Ctrl+ Shift + Alt + F9. (That is, every modifier key.) It not only recalculates but rebuilds the dependency tree as well. From what you're describing it's possible that your multi-sheet edit caused a problem in the tree. It shouldn't in theory, but in practice... y'never know.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

pandinus
Posts: 77
Joined: Tue Mar 18, 2014 8:02 am
OLAP Product: TM1, Cognos Express
Version: 10.2.2
Excel Version: 2013

Re: Cells not calculating unless edited

Post by pandinus » Wed Nov 22, 2017 10:34 am

This can also happen when you somewhere have a DBRW formula which points to an intersection in the cube that is not part of the VIEW formula that you refer to in the DBRW.

NPTM1
Posts: 7
Joined: Mon Sep 26, 2016 9:59 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2010

Re: Cells not calculating unless edited

Post by NPTM1 » Wed Nov 22, 2017 2:31 pm

The CTRL+ALT+SHIFT+F9 worked, definitely something I'll keep in mind, many thanks Alan.

Post Reply