Count the number of Accounts for each agency

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

Count the number of Accounts for each agency

Post by Analytics123 » Fri Oct 20, 2017 7:22 pm

Hi ,

I have a hierrachy like Region, Agency,Subregion, Rep and accounts .

The user wants the count of number of accounts under each agency .

Is there a TI function I can use which will take the agency as input and give me the count of accounts under that agency .


Thanks,

User avatar
tomok
MVP
Posts: 2349
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: Count the number of Accounts for each agency

Post by tomok » Fri Oct 20, 2017 7:32 pm

Analytics123 wrote:
Fri Oct 20, 2017 7:22 pm
Is there a TI function I can use which will take the agency as input and give me the count of accounts under that agency .
No, but you can always write your own. If it was me I would have a TI that takes the agency as an input, builds an MDX-based subset on the dimension that does a TM1DRILLDOWNMEMBER on that input agency and then filters to only 0 level. Then you can use SUBSETGETSIZE on that subset. That will give you the number of accounts under the agency.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

BariAbdul
Regular Participant
Posts: 404
Joined: Sat Mar 10, 2012 1:03 pm
OLAP Product: TM1,Cognos
Version: 9.5.2
Excel Version: 2007

Re: Count the number of Accounts for each agency

Post by BariAbdul » Sat Oct 21, 2017 3:48 am

Please go through the MDX primer.doc for detail explaination of MDX functions with context to TM1. Thanks
Attachments
MDX_Primer.doc
(669 KiB) Downloaded 6 times
"You Never Fail Until You Stop Trying......"

Post Reply