Thank you George, Chris and Wim,
I'll admit that I could not have told you some of these things without verifying it by looking at the code first. There is an unrelated ask for a list of all functions out there as well which I'd hoped to answer with TM1 12 (read: our new LOLAP based MDX engine) but that might be a tad too far out. I'm already looking at the list of function we support in there that we don't support in the existing (old) MDX engine as well as if it were easy to plug some of those holes.
To answer Chris's question, no, that above was only my response to Wim's list, there could definitely be more, like the IsEmpty that I told you about (as opposed to a IsNull). Again, looking at what can be done and I will get that list of what's there clear and I'll be happy to share when I do.
Original Message:
Sent: Tue April 26, 2022 11:10 AM
From: George Tonkin
Subject: Absolute value function in MDX queries
VAL will work too:
FILTER({TM1SUBSETALL( [Period] )}, VAL(LEFT([Period].CurrentMember.Name,4))=2022)
What seems to be very useful is you can use VAL against a numeric attribute e.g.
FILTER({TM1SUBSETALL( [Scenario] )},VAL([Scenario].CurrentMember.Properties("Flag"))=1)For C levels and other N levels where flag was never specified and shows a 0, these do not result in an error like StrToValue and the need to use IIF() to trap for a null and set it to 0 before converting.
Changing the above MDX to filter on a value of 0 returns those members either set to 0 or never set to begin with.
------------------------------
George Tonkin
Original Message:
Sent: Tue April 26, 2022 11:07 AM
From: Chris Courim
Subject: Absolute value function in MDX queries
Thank you Wim and George!
It's extremely difficult to tell when the only error message you get is "invalid MDX expression".
I must have a syntax error somewhere.
Thanks again.
------------------------------
Chris Courim
Original Message:
Sent: Tue April 26, 2022 10:50 AM
From: Wim Gielis
Subject: Absolute value function in MDX queries
Hi Chris,
LEN seems to work in MDX.
------------------------------
Wim Gielis
Original Message:
Sent: Tue April 26, 2022 10:36 AM
From: Chris Courim
Subject: Absolute value function in MDX queries
Hubert-
Unfortunately, it is really hard to determine what VBA style functions are supported within TM1 MDX as they are not listed in the online documentation.
Support for Microsoft-defined MDX Expressions and Functions – IBM Documentation
It probably should be updated to include the functions you mention above like INSTR, LEFT, RIGHT.
The list of requested functions above along with Wim suggestion for Replace or Substitute would be very useful.
Would it be possible for you to provide a list of the VB style functions that are supported? That way we would be educated as to which ones are supported and we could update the list with new ones. (Or is the list you provided above complete?)
In the interim, do you know or anyone else know, if the LEN function is supported in TM1 MDX? I need it for a project I'm woking on at the moment. I'm trying to query a cube cell and determine whether it is populated are not. Maybe there something like an ISNULL?
------------------------------
Chris Courim
Original Message:
Sent: Mon April 25, 2022 02:38 PM
From: Wim Gielis
Subject: Absolute value function in MDX queries
Hello (again) Hubert,
That's an interesting read (about things that we cannot otherwise know but only guess).
I agree that functions can already exist with a different name (alias) but return the same result. I tried some of the list
I have no knowledge of the Generations function and therefore would not know what to expect from it.
Any regular expression options that you could give us would be much appreciated. A former colleague of mine wrote his own TI processes to serve as functions to process regular expressions logic. Avoiding convoluted workarounds would be so much better.
Now when we are asking for new functions (more in TI and rules): Replace or Substitute, anyone ? :-)
Tools like Jedox simply offer Replace, even regex-based, out of the box.
It always feels like we are thrown 30 years back in time with our Insrt and Delet functions ;-)
Left and Right in TI and rules would be handy too.
------------------------------
Wim Gielis
Original Message:
Sent: Mon April 25, 2022 07:05 AM
From: Hubert Heijkers
Subject: Absolute value function in MDX queries
Hi Wim,
There is a bunch here for which the functionality already exists in TM1:
- Caption is merely an alias for the 'Name' dot function, which TM1 does have, and which seems to be DB2 specific
- Count exists in TM1 but we only support the standard syntax and not the alternative <<Set>>.Count syntax
- Description appears to be another alias for the 'Name' dot function , and which again seems to be DB2 specific
- Edge is an alias for Crossjoin for which TM1 has the function as well as supporting the alternative * syntax (read: another alias function)
- NonEmptyCount is shorthand (read: kind of an alias) for Count(NonEmpty( <<set>>, <<numeric-expression>> ))
That leaves us with:
- Generations would be an interesting topic (both DB2 and Oracle Essbase have the notion of generations), but given the fact that in TM1 we already have MDX Levels and TM1FilterByLevel, both using a different definition of level, it would be interesting to define what Generations would be, especially because TM1 has no support for raggedness which, using DB2's definition, would make Generations the same as Levels in TM1.
- Int (see Round below) would be the same as Round( <<numeric>>, 0 ) and I'd prefer it that way as, at least for now, TM1 has no build in notion of integer values (all numeric values are floating point values).
- Mid, I knew I had seen some SubString implementation in the code so I checked. Turns out we have both Left and Right functions already! Combining these two obviously could be used to implement a Mid but wouldn't it be nice to just have Mid indeed that did that for you... (added it to the wish-list;-)
- PatternMatches, with true RegEx support, would be a step beyond what TM1FilterByPattern does, apart from the fact that I like it much better as a function that operates on a string, and as such can be used in any form of expression (most notably Filter expressions) then a set operation like TM1FilterByPattern is. On a positive note, TM1FilterByPattern will perform much better, especially on larger sets, come TM1 11.9/12 ;-)
- Round[-Down/Up] understood, added to the wish-list as well!
------------------------------
Hubert Heijkers
Original Message:
Sent: Fri April 22, 2022 02:44 PM
From: Wim Gielis
Subject: Absolute value function in MDX queries
Hello,
When I look at the link for the DB2 documentation, at least the following are missing I think:
- Abs
- Caption
- Count
- Description
- Edge
- Generations
- Int
- Mid
- NonEmptyCount
- PatternMatches
- Round
- RoundDown
- RoundUp
Abs, Int, Round and Mid will be useful in particular but the other functions will have their use cases as well.
If PatternMatches means true RegEx support, that would be great to have :-)
For instance, permant public subset names satisfying a regex pattern and can be cleaned up because someone was too lazy in the past to use temporary subsets and now thousands of subsets exist in the model.
------------------------------
Wim Gielis
Original Message:
Sent: Fri April 22, 2022 02:18 PM
From: Hubert Heijkers
Subject: Absolute value function in MDX queries
Hi Chris,
Busy is good not ;-? But yes, TM1 12 has my focus right now but we are continuously making improvements tour our current V11 LTSR as well!
What would the list of 'these functions' be that you are looking for? Apart from 'Abs' you'd like a 'Now' and some kind of 'Format' function, neither of which we currently have either AFAIK from the top of my head? And I'm guessing you are looking at things like Pow(er) and Sqrt and other mathematical operations as well perhaps?
------------------------------
Hubert Heijkers
Original Message:
Sent: Fri April 22, 2022 09:18 AM
From: Chris Courim
Subject: Absolute value function in MDX queries
Hubert –
I know you're very busy man working on the next generation TM1 server but what would it take to get these functions added into MDX?
Here's another a simple and quite useful real world example where the addition of these functions would be very helpful..
This works
STRTOMEMBER('[Date].[ Date].[' + '2020-05-20' + ']')
Unfortunately, trying to make the same dynamic by using the Now function doesn't.
STRTOMEMBER('[Date].[ Date].[' + Format(Now(),'yyyy-mm-dd') + ']')
I know there are several workarounds but having these functions would really make developing robust applications much easier.
------------------------------
Chris Courim
Original Message:
Sent: Fri April 22, 2022 05:45 AM
From: Wim Gielis
Subject: Absolute value function in MDX queries
Thank you both !
------------------------------
Wim Gielis
Original Message:
Sent: Fri April 22, 2022 04:01 AM
From: Hubert Heijkers
Subject: Absolute value function in MDX queries
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
Original Message:
Sent: Thu April 21, 2022 05:13 PM
From: Wim Gielis
Subject: Absolute value function in MDX queries
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
------------------------------
#PlanningAnalyticswithWatson