Suppress zero PAX

Post Reply
fstuyck
Posts: 18
Joined: Thu Sep 03, 2020 1:59 pm
OLAP Product: TM1
Version: 2.0
Excel Version: office 365

Suppress zero PAX

Post by fstuyck »

Hi,

I am having an issue with supress zeros in a dynamic report.
When the first column element is typed, supress zero’s works correctly.
When the first column element is selected via a SUBNM("PeriodMonthly";"Base";"202101") dropdown,
the other column elements are no longer taken into account.

How does the suppress zero functionality recognize column values?
Any Ideas?
Attachments
Select period.png
Select period.png (3.71 KiB) Viewed 4737 times
Type period.png
Type period.png (4.82 KiB) Viewed 4737 times
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Suppress zero PAX

Post by burnstripe »

Columns don't get suppressed in an active form only rows. Is the subnm correct, I would have expected the commas , instead of semicolons ; to separate the conditions

I don't usually have a subnm directly on a column but I frequently have columns derived from a single subnm, so you can select one period and it displays say the next 12 periods.

After making the selection in the subnm are you simply refreshing or rebuilding the book

Just a thought tin the tm1rptview what are your title elements. And in the dbrw are you referencing the cell with tm1rptview or have you just named the cube name. If you just referenced the cube name that'll be a problem as the zero suppression.

It's a bit difficult to give too many pointers though without seeing the spreadsheet and what the formulae and named rangess are
fstuyck
Posts: 18
Joined: Thu Sep 03, 2020 1:59 pm
OLAP Product: TM1
Version: 2.0
Excel Version: office 365

Re: Suppress zero PAX

Post by fstuyck »

Hi,

Thanks for the response.
Usually, I derive 12 periods from a single subnm selection cell.
The problem that I am having is that the column (period) information is not used as input, unless I explicitly type a period in one of the cells.

Some additional information:
- My ranges go far enough.
- The period dimension is not specified in the tm1rptview.
- The DBRW formulas are referencing the cell with tm1rptview
- I rebuild the book.
- The subnm works fine.
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: Suppress zero PAX

Post by Steve Rowe »

Hi,

How are you getting to the outer months from your first month?

I suspect there is an issue with the calculation order where your column headers are not fully resolved at the time the dynamic report is initially populated. Pafe uses Excels dirty cell flagging to determine what needs to be calculated versus perspectives where everything is just calculated. This can mean that approaches that worked in Perspectives don't in Pafe, this can usually be resolved with different approaches.

Try using Excel Validations to drive the pick list rather than the subnm. The Excel validtion list could be populated using Subnms against your Base subset.
HTH
Technical Director
www.infocat.co.uk
fstuyck
Posts: 18
Joined: Thu Sep 03, 2020 1:59 pm
OLAP Product: TM1
Version: 2.0
Excel Version: office 365

Re: Suppress zero PAX

Post by fstuyck »

Hi,

Your solution worked.
It must indeed have been an issue with the calculation order at the time the dynamic report is initially populated.
Steve Rowe wrote: Tue Jun 08, 2021 10:47 am Try using Excel Validations to drive the pick list rather than the subnm. The Excel validtion list could be populated using Subnms against your Base subset.
I added an excel data validaiton that references a list created using the TM1ELLIST function.
This list was then referencet by my first column header.
The other periods are just cell_on_left + 1.

Thanks a lot, for the support
Post Reply