Planning Analytics

 View Only
  • 1.  IBM Web not supporting Excel FILTER formula

    Posted Wed October 19, 2022 09:11 AM
    I am using the excel FILTER function in PAX and works perfect, but when i publish it to TM1 Web it display error value as #NAME?

    Also, i checked the supported and unsupported 'Microsoft Excel functions - TM1 Web' list which doesn't include FILTER ​function

    Any suggestion how to resolve this issue

    ------------------------------
    Sudeep Rimal
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: IBM Web not supporting Excel FILTER formula

    Posted Wed October 19, 2022 06:03 PM
    You've answered your own question. I don't think the list of _un_supported functions is meant to be complete. If it's not in the supported list (I agree it isn't) then it won't work - which you have confirmed.

    ------------------------------
    David Usherwood
    ------------------------------



  • 3.  RE: IBM Web not supporting Excel FILTER formula

    Posted Fri October 21, 2022 11:15 AM
    Thank for your answer David, i want to it in more detail of what i did
    I wanted to have a searchable drop-down list in PAX and then publish the report in TM1 web

    I followed the regular Excel formula
    First i did with Filter formula, which gave me a NAME error when i published it in TM1 Web  

    =SORT(FILTER(C4:C32,ISNUMBER(SEARCH(E2,C4:C32))))

    Since FILTER was not listed as supported Function, I tried including TM1 Web supported functions (INDEX, ROW and ROWS) as below which was listed in IBM Documentation. This also show an Error when published in TM1 Web.

    =IFERROR(INDEX($C$4:$C$32,SMALL(IF(ISNUMBER(SEARCH($E$2,$C$4:$C$32)),ROW($C$4:$C$32)-ROW($C$4)+1),ROWS($G$4:G4))),"")

    Both the formula gave an expected result in PAX but did not work in TM1 Web.


    Any suggestion what i did wrong here that TM1 is not liking it

    Sudeep



    ------------------------------
    Sudeep Rimal
    ------------------------------



  • 4.  RE: IBM Web not supporting Excel FILTER formula

    Posted Sat December 31, 2022 05:03 PM

    TM1Web does not support the IFERROR or ISNUMBER functions unfortunately.  Information functions - IBM Documentation 

    Is there a way for you to utilize MDX functions or create other measures in Planning Analytics to support your report requirement? 



    ------------------------------
    Bruna Garcia
    Master Consultant
    QueBIT Consulting
    ------------------------------