replace a subset value in db function

Post Reply
Analytics123
Posts: 84
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 » Thu Oct 12, 2017 12:27 pm

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: 3505
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: replace a subset value in db function

Post by jim wood » Thu Oct 12, 2017 12:32 pm

Lookup up cube may be?
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 7 64-bit. TM1 Version: 10.1.1

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: replace a subset value in db function

Post by tomok » Thu Oct 12, 2017 12:44 pm

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
Posts: 12
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: 11.2
Excel Version: 2016

Re: replace a subset value in db function

Post by ascheevel » Thu Oct 12, 2017 12:45 pm

Consolidation called 'Prior Month' would work too.

User avatar
jim wood
Site Admin
Posts: 3505
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: replace a subset value in db function

Post by jim wood » Thu Oct 12, 2017 4:36 pm

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.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 7 64-bit. TM1 Version: 10.1.1

Post Reply