replace a subset value in db function

Post Reply
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

replace a subset value in db function

Post by Analytics123 »

Hi,

I have to replace a subset value in place of hard coded month in the below DB expression

['Average DSO'] =DB('AR History', !Customers SoldTo Only, 'US01', 'Jul 2017', 'All Currencies', 'Local Currency', 'ASSETS', 'All Customer Types', '6 month DSO');

I have a subset name called Months - Prior Month which has one item .

If we cannot use the dynamic subset in rules, how can i generate the last completed month which is sep 2017 with in rule.

Thanks,
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: replace a subset value in db function

Post by jim wood »

Lookup up cube may be?
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
tomok
MVP
Posts: 2831
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: replace a subset value in db function

Post by tomok »

You have two options that I can think of; 1) have a lookup cube (as has already been mentioned), or 2) create an alias on the time dimension and make the alias value for the current month equal to "Current Month". That way you can reference "Current Month" in your rule. When the next month rolls around you take off the alias for the old month and make the alias value for the next month be "Current Month". They both work so it comes down to personal preference.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: replace a subset value in db function

Post by ascheevel »

Consolidation called 'Prior Month' would work too.
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: replace a subset value in db function

Post by jim wood »

I always lean towards a lookup cube as it can be used within multiple cubes if you don't have a shared time dimension or even multiple time dimensions within the same cube.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Post Reply