Planning Analytics

 View Only
  • 1.  TM1ELLIST

    IBM Champion
    Posted Wed May 11, 2022 06:39 PM
    Edited by System Fri January 20, 2023 04:16 PM
    IMO the documentation on TM1ELLIST should be updated to reflect Excel's dynamic array behavior.

    TM1ELLIST

    In fact, in Excel (connected through PAfE) you can just enter in a cell like A1:

    =TM1ELLIST("CXMD:}Clients",,,,,"Head( TM1SubsetAll( [}Clients] ), 10 )")

    And the result will spill down in the cell and 9 cells below - unless you have less than 10 users in the model.
    Change the servername as well.

    No need for array-entering the formula with Ctrl-Shift-Enter to receive the curly braces left and right of the formula.
    You now enter the world of dynamic arrays in Excel.

    For instance, in B1 enter:

    =SORT( A1#, , -1 )

    to sort the list (however many rows, 10 or less) descending.

    If we could now also have the dynamic array reference (hash symbol) work in TM1 formulas like DBRW, or SUBNM or any other, that would be the best !​

    For those using VBA to generate the formula, consider:

    Sub create_ellist()
    
        Dim vMDX As String
        
        vMDX = "TM1FilterByLevel( TM1SubsetAll( [Year] ), 0 )"
    
        'creating a TM1ELLIST function as a dynamic array
        Range("A1").Formula2 = "=TM1ELLIST(""servername:Year"",,,,,""" & vMDX & """)"
        Range("B1").Formula2 = "=""Y"" & A1#"
    
    End Sub​


    ------------------------------
    Wim Gielis
    ------------------------------
    #PlanningAnalyticswithWatson


  • 2.  RE: TM1ELLIST

    IBM Champion
    Posted Thu May 12, 2022 12:01 AM

    Incorporating the support of DA formulars for DBRW would be a game change in my opinion.






  • 3.  RE: TM1ELLIST

    Posted Thu May 12, 2022 11:22 AM

    It's true that TM1ElList's implementation in PA for Excel was pre-suited for the addition of spilling/dynamic arrays to Excel. Pretty happy about that, didn't require additional work on our side. Will look into having the remarks about usage updated, but would note that consumption of array outputs remains a fairly complex topic, and support is not wholly propagated across all the versions of Excel that the addin supports, unlike CSE arrays.

    Regarding dynamic array reference consumption, this is something we need to get a sense of demand for, and would be a better candidate for our upcoming functions instead of a retrofit to the existing ones. The formula registration signature has a significant impact on performance, so would need to tread carefully, I'm not sure if the existing signature would allow for this.



    ------------------------------
    Ted Phillips
    IBM
    ------------------------------



  • 4.  RE: TM1ELLIST

    Posted Thu May 26, 2022 06:27 PM
    100% agree that ultimately having other TM1 formulas (DBRW, SUBNM, etc.) be dynamic array aware would be awesome. I recently dealt with practical issue when I used TM1ELLIST to return a dynamic array of elements. WHOA!  AWESOME!. 

    But wait, I see that TM1ELLIST in PAfE does not support specifying an alias and since I needed the alias, I had to use resort to using a SUBNM to reference each individual item returned by TM1ELLIST. Now we're back to old school since I had to copy the SUBNM formula down, which obviously isn't dynamic. So TM1ELLIST is a teaser but slammed me back down to earth :)

    I do question the rationale for TM1ELLIST in PAfE NOT supporting specifying an alias since it would ssem to be the typical use case that the list would be the source for a dropdown of some sort for the user interface. Also, TM1Web doesn't support dynamic arrays so it can't seem to be used there either.  

    Hope I'm missing something obvious because something like TM1ELLIST is a total game changer but but seems to be severely limited in its practical application?


    ------------------------------
    Mitchell Powell
    ------------------------------