Active form hell

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Active form hell

Post by Wim Gielis » Thu Sep 10, 2020 3:11 pm

Thank you Mark,

Now I can reproduce it. This is very unfortunate, honestly.
05.png
05.png (77.34 KiB) Viewed 160 times
Input on March, Y2 and March Y1 is visible. Who invented this ?
I should now expect phone calls by customers I guess, since I never paid attention to that "design decision".
Last edited by Wim Gielis on Thu Sep 10, 2020 3:13 pm, edited 1 time in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

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

Re: Active form hell

Post by Mark RMBC » Thu Sep 10, 2020 3:12 pm

Hi Wim,

All I can say is it wasn't me!

regards, Mark

tomok
MVP
Posts: 2751
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: Active form hell

Post by tomok » Thu Sep 10, 2020 5:00 pm

Wim Gielis wrote:
Thu Sep 10, 2020 3:11 pm
Input on March, Y2 and March Y1 is visible. Who invented this ?
I should now expect phone calls by customers I guess, since I never paid attention to that "design decision".
AFAIK, it has always worked this way. This is actually one of the drawbacks of using separate year and month dimensions in TM1, that rolling 13 month presentations like this don't zero suppress in the manner you would probably like.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Active form hell

Post by Wim Gielis » Thu Sep 10, 2020 5:56 pm

tomok wrote:
Thu Sep 10, 2020 5:00 pm
This is actually one of the drawbacks of using separate year and month dimensions in TM1, that rolling 13 month presentations like this don't zero suppress in the manner you would probably like.
I can understand this for months and periods, but the problem goes much further. Just think about Scenario. Every (financial) report could use some sort of selection of scenario and year/month in the columns: actuals YTD by period, budget next year at total year level, the latest forecast for the coming months at the month level, and so on. I cannot understand this design choice. If I have budget data for January next year, why should that drive my rows in an actuals report (month by month) if it happened to be the case that we didn't have actuals this year (and if scenario is part of the columns area of the active form, and if next year is used in the active form as an element).

Am I right when I say that:
- we can never have too few rows (rows that should have been there in my selection but that drop)
- but what can happen is to have an excess of rows (that are 0 and should be have been suppressed) ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Silverik
Posts: 3
Joined: Thu Sep 10, 2020 12:29 pm
OLAP Product: TM1
Version: Latest
Excel Version: 365

Re: Active form hell

Post by Silverik » Fri Sep 11, 2020 7:32 am

Wim Gielis wrote:
Thu Sep 10, 2020 5:56 pm
tomok wrote:
Thu Sep 10, 2020 5:00 pm
This is actually one of the drawbacks of using separate year and month dimensions in TM1, that rolling 13 month presentations like this don't zero suppress in the manner you would probably like.
I can understand this for months and periods, but the problem goes much further. Just think about Scenario. Every (financial) report could use some sort of selection of scenario and year/month in the columns: actuals YTD by period, budget next year at total year level, the latest forecast for the coming months at the month level, and so on. I cannot understand this design choice. If I have budget data for January next year, why should that drive my rows in an actuals report (month by month) if it happened to be the case that we didn't have actuals this year (and if scenario is part of the columns area of the active form, and if next year is used in the active form as an element).

Am I right when I say that:
- we can never have too few rows (rows that should have been there in my selection but that drop)
- but what can happen is to have an excess of rows (that are 0 and should be have been suppressed) ?
Yes that's correct...

For the AF behaviour, it always have been like that.

By having:
- a continuous time dimension,
- and a "Current Forecast" version combining Actuals and Forecast to give you a full year picture,
You avoid most of these traps you mentionned.

i.e. to display Current year 12 Month Actual/Forecast.
Without a combined "current Forecast version" you need: 12 months and 2 versions (Actual, Forecast) = 24 combinations but only 12 displayed ---> you expose yourself to the issue
With a combined "Current Forecast" version you need: 12 months and 1 version = 12 combinations --> No risk there

i.e. to display rolling forecast, next 12 months accross FY
With 2 dimensions ("Year" and "Month") : you need 12 months and 2 Fiscal years = 24 combinations but only 12 displayed ---> you expose yourself to the issue
With a single time dimension ("Year Month") you only need 12 periods = 12 combinations --> No risk there, and actualy an additional benefit of a single time dimension, you can use a simple consolidation in the cube to see your rolling forecast....

You will still probably face some case where this is happening. You'll need to find some workaround, educate users.

But I agree this is a bit anoying but that's the way it is...

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Active form hell

Post by Wim Gielis » Fri Sep 11, 2020 9:07 am

Many thanks Silverik, good information and glad that you joined the forum 😉

Thanks Tomok too.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Active form hell

Post by Wim Gielis » Fri Sep 11, 2020 10:01 am

A follow up if I may, regarding the continuous time dimension: let's have concatenated year/period elements like 202008, 202009.
Suppose the users input values on months but in a grid: years across rows, periods across columns.

I am not talking about active forms here, but rather PAW or PAFE. In the periods dimension, there are (PA-type) hierarchies for months and years.
So, by crossing the 2 hierarchies, we can have a grid of suppose: 3 years by 12 columns ==> 36 periods/cells.
But, all cells are consolidated by definition, with month elements underneath.
02.png
02.png (4.56 KiB) Viewed 120 times
Does this mean that C-level rules are out of the question ?
Does this mean that data spreading at C-level cannot be blocked ?
Does this mean that security (typically, blocking certain periods for WRITE) should be revisited ?

Or, rather, are we restricted to: 36 months at N-level in the rows rather than 3*12 cells in a grid ?
Or, maybe we need a 2 dimensional input cube and rules/TI ?

A lot of questions. I'm going to explore that but if someone can already share their findings, much appreciated.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Active form hell

Post by Wim Gielis » Sat Sep 12, 2020 12:10 pm

Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

rbhansen22
Posts: 2
Joined: Thu Jun 25, 2020 8:32 pm
OLAP Product: Planning Analytics
Version: 2.0.9.1
Excel Version: Office 365

Re: Active form hell

Post by rbhansen22 » Sun Sep 13, 2020 11:40 am

Hi all,

Active forms in Perspectives zero suppress on what would be the the symmetric view created from the elements referenced ... so if you have a stacked column axis ... even if you’ve removed some combinations, the symmetric view will still contain them which is why the view test is helpful.
Note that in PA for Excel that is not the case ... as it’s now calculating with MDX the zero suppression is a true zero suppression of what is in the form. (Also be aware that TM1 web still uses the older mode so you could end up with some differences if publishing).

Regardless, the zero suppression will only happen in the columns identified as part of the active form. If you don’t want the elements included in zero suppression, do not have the 1st parameter of the dbrw point them back to the active form view formula and just add a straight cube reference (or if you have a lot of extra columns in pre PA for Excel or if publishing you will want to create a separate view statement to reference for performance reasons).

Rebecca

Wim Gielis
MVP
Posts: 2527
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Active form hell

Post by Wim Gielis » Mon Sep 14, 2020 5:31 am

Thank you Rebecca
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Post Reply