Counting element instances
- Apr. 04, 2010
Introduction
Some time ago, I was thinking how one could count the number of occurrences of an element in a dimension. In TM1, each and every element (consolidated element, numeric element, string element) should be unique throughout the entire dimension. But you can use the same element any number of times.
Months of year
The classic example is the case of 12 lowest level elements (n) for the months of the year. Next to that, you could easily create roll-ups for Year-to-date calculations. The cube data are only stored on the 12 n-type elements, however they are shown and used in the YTD calculations.
A TI approach
According to me, the easiest way is to generate a series of subsets, one for each element. Then, the SUBSETGETSIZE function in Turbo Integrator returns the number of elements in the subset.
Below is the code for the Advanced > Prolog tab in a TI process.
# Wim Gielis # https://www.wimgielis.com##### # This code will fill an attribute with the number # of times an element occurs in a dimension # 04/04/10 ###### variables for ease of understanding and elegant coding # fill this in correctly vDimension='…'; vAttribute='ElementCount'; vSubset='MySubset'; # Recreate the attribute to store the results ATTRDELETE(vDimension,vAttribute); ATTRINSERT(vDimension,'',vAttribute,'N'); # loop through the dimension elements i=1; WHILE(i<=DIMSIZ(vDimension)); vElement=DIMNM(vDimension,i); SUBSETCREATEBYMDX(vSubset,'{TM1FILTERBYPATTERN(TM1DRILLDOWNMEMBER( TM1SUBSETALL( [' | vDimension | ']), ALL, RECURSIVE), "' | vElement | '")}'); ATTRPUTN(SUBSETGETSIZE(vDimension,vSubset),vDimension,vElement,vAttribute); SUBSETDESTROY(vDimension,vSubset); i=i+1; END;
Watch out!
Within the loop over the dimension elements, the MDX statement above will always be evaluating correctly, because for sure at least one element will be found for that subset. If not, such MDX statements would generate an error and bomb the process. See here for a short discussion of this problem.
