Planning Analytics with Watson

 View Only
Expand all | Collapse all

Absolute value function in MDX queries

  • 1.  Absolute value function in MDX queries

    Posted Thu April 21, 2022 05:13 PM
    Edited by Wim Gielis Fri April 22, 2022 02:38 PM
    Hello all,

    When creating a set in PAW with MDX as follows, it works:

    FILTER([Db_Rayon].[Db_Rayon].MEMBERS , [Ugp_Db_Rayon].([Ugp_Db_Rayon_kg].[getal]) > 0)

    Adding in the Abs (Absolute value) function, we get an error message:

    FILTER([Db_Rayon].[Db_Rayon].MEMBERS , Abs( [Ugp_Db_Rayon].([Ugp_Db_Rayon_kg].[getal])) > 0)

    ("Invalid MDX expression")

    When looking at this page: Abs function (MDX) the function is supported for IBM DB2 - maybe not supported for PAW ?

    One step further, when using Abs( ) in an MDX view query, there is no error message but after applying the query with Abs, PAW ignores the part on Abs, removes it from the query and does as if it was not added by the user.

    For example, this query works:

    SELECT {[Measure].[Measure].[getal]} ON 0, {FILTER([Db_Rayon].[Db_Rayon].[Midden].CHILDREN , [Ugp_Db_Rayon].([Measure].[Measure].[getal]) > 0)} ON 1 FROM [Ugp_Db_Rayon]

    When I add the Abs( ):

    SELECT {[Measure].[Measure].[getal]} ON 0, {FILTER([Db_Rayon].[Db_Rayon].[Midden].CHILDREN , Abs( [Ugp_Db_Rayon].([Measure].[Measure].[getal])) > 0)} ON 1 FROM [Ugp_Db_Rayon]

    PAW reverts back to the first version of the query, ignoring my request to add in Abs.

    Can someone shed light on what we can use or not ? Why would it not be supported in PAW ?

    I can get the absolute value to work with the workaround of 2 conditions (  >  x OR < -x ) but if such a function exists, why not use it ?

    Thanks !

    ------------------------------
    Wim Gielis
    ------------------------------


  • 2.  RE: Absolute value function in MDX queries

    Posted Thu April 21, 2022 05:49 PM
    Put differently, this works:

    WITH MEMBER [dim].[dim].[number_2] AS [dim].[dim].[number] + 100 SELECT {[Db_Rayon].[Db_Rayon].[test]} ON 0, {[dim].[dim].[number_2],[dim].[dim].[number]} ON 1 FROM [cube]

    but this does not work:

    WITH MEMBER [dim].[dim].[number_2] AS Abs( [dim].[dim].[number] ) SELECT {[Db_Rayon].[Db_Rayon].[test]} ON 0, {[dim].[dim].[number_2],[dim].[dim].[number]} ON 1 FROM [cube]

    The difference does not seem very shocking to be honest.
    In case 1, I add a value of 100. In case 2, I ask for the absolute value.

    ------------------------------
    Wim Gielis
    ------------------------------



  • 3.  RE: Absolute value function in MDX queries

    IBM Champion
    Posted Fri April 22, 2022 04:32 AM
    Hi Wim,

    I'm sure you will have found a workaround yourself but for anyone else who stumbles on this in the future.
    In the absence of an ABS function there are a few different ways to create your own.

    So instead of:

    WITH MEMBER [dim].[dim].[number_2] AS Abs( [dim].[dim].[number] ) SELECT {[Db_Rayon].[Db_Rayon].[test]} ON 0, {[dim].[dim].[number_2],[dim].[dim].[number]} ON 1 FROM [cube]

    You could use:

    WITH MEMBER
    [dim].[dim].[number_2]
    AS [dim].[dim].[number] * IIF ( [dim].[dim].[number] < 0, -1, 1 )
    SELECT
    {[Db_Rayon].[Db_Rayon].[test]} ON 0,
    {[dim].[dim].[number_2],[dim].[dim].[number]} ON 1
    FROM [cube]



    ------------------------------
    Declan Rodger
    Technical Director
    Spitfire Analytics
    ------------------------------



  • 4.  RE: Absolute value function in MDX queries

    Posted Fri April 22, 2022 04:01 AM
    Edited by Hubert Heijkers Fri April 22, 2022 04:06 AM
    Hi Wim,

    The truth of the matter is that ABS is not an MDX function but in the MSAS world it was available because it was in the VB function library that was included in Analysis Services.

    The documentation for the ABS function you reference, is, as you mention as well, DB2 specific. Quickly looking at that list (and I kind of know how that list came about as I was a consultant to IBM for DB2 cubing service in the early 2000's myself;-), it looks like a blend of a subset of MDX and a subset of those VB functions that they ended up choosing to support. I for one would have no objection to adding a bunch of these to TM1 one of these days ;-).

    So, unfortunately the 'Invalid MDX expression' is correct here, albeit a nicer error indicating that Abs is not a valid keyword or function would have been a tad more informative perhaps.

    ------------------------------
    Hubert Heijkers
    ------------------------------



  • 5.  RE: Absolute value function in MDX queries

    Posted Fri April 22, 2022 05:46 AM
    Thank you both !

    ------------------------------
    Wim Gielis
    ------------------------------



  • 6.  RE: Absolute value function in MDX queries