MDX Query Using Right Two Positions of a Global Variable

Post Reply
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

MDX Query Using Right Two Positions of a Global Variable

Post by michaelc99 »

Good Morning Everyone,

I am in the process of writing an MDX expression that filters my PERIOD MONTH MAIN dimension based on two conditions. The first being where the attribute of FORECAST MONTH contains "FORECAST" and the second condition based on a wildcard search for "22" which reflects 2022 periods. I used the expression recorder and the following code (below) returned. The code works perfectly and if I update "22" to "23" then the expression returns all "2023" periods.

Code: Select all

 
 {TM1FILTERBYPATTERN( {FILTER( {TM1SubsetBasis()}, [PERIOD MONTH MAIN].[FORECAST MONTH] = "FORECAST")}, "*22")}
 
However, I want to change "22" to be based on the right two positions of a Global Variable called "Current Year". I've attempted a number of ways to accomplish this; however, while the syntax of the code below is valid, it returns results based only on condition 1. Would INSTR be a better option versus trying to RIGHT(global variable, 2)?

Code: Select all

{FILTER( {TM1SUBSETALL( [PERIOD MONTH MAIN] )}, [PERIOD MONTH MAIN].[FORECAST MONTH] = "FORECAST")}, "*
RIGHT([GLOBAL VARIABLE].([GLOBAL VARIABLE].[Current Year],[GLOBAL VARIABLE MEASURE].[String]))), 2)"}
Thank you in advance,
Michael
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: MDX Query Using Right Two Positions of a Global Variable

Post by gtonkin »

You could try something like this:

Code: Select all

{FILTER(
    {FILTER(
       	{
          TM1SUBSETALL( [PERIOD MONTH MAIN] )},
          [PERIOD MONTH MAIN].[FORECAST MONTH] = "FORECAST")
       	},
     	RIGHT([PERIOD MONTH MAIN].[PERIOD MONTH MAIN].CURRENTMEMBER.NAME,2)=[GLOBAL VARIABLE].([GLOBAL VARIABLE].[Current Year])
     )}
)}
No guarantees - check brackets etc. as I have just done some copy/paste/fiddling.
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: MDX Query Using Right Two Positions of a Global Variable

Post by michaelc99 »

gtonkin wrote: Thu Sep 01, 2022 2:21 pm You could try something like this:

Code: Select all

{FILTER(
    {FILTER(
       	{
          TM1SUBSETALL( [PERIOD MONTH MAIN] )},
          [PERIOD MONTH MAIN].[FORECAST MONTH] = "FORECAST")
       	},
     	RIGHT([PERIOD MONTH MAIN].[PERIOD MONTH MAIN].CURRENTMEMBER.NAME,2)=[GLOBAL VARIABLE].([GLOBAL VARIABLE].[Current Year])
     )}
)}
No guarantees - check brackets etc. as I have just done some copy/paste/fiddling.
Thank you for your fast response!

I used you code and made a few tweaks so that the right two positions of "JUN 22" would match the right two positions of "2022" but I am receiving an "Incorrect Parameter" message (see attachment). I'll dig into it some more and see what I am possibly missing. I wonder if I need to define "Current Year" as a string with "[Current Year],[GLOBAL VARIABLE MEASURE].[String]" included.

Code: Select all

{FILTER(
    {FILTER(
       	{
          TM1SUBSETALL( [PERIOD MONTH MAIN] )},
          [PERIOD MONTH MAIN].[FORECAST MONTH] = "FORECAST")
       	},
     	RIGHT([PERIOD MONTH MAIN].[PERIOD MONTH MAIN].CURRENTMEMBER.NAME,2)=RIGHT([GLOBAL VARIABLE].([GLOBAL VARIABLE].[Current Year]),2)
     )}
Attachments
incorrectparameter.png
incorrectparameter.png (3.35 KiB) Viewed 1145 times
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: MDX Query Using Right Two Positions of a Global Variable

Post by michaelc99 »

Good Morning All,

Sorry for the double-post.

That is exactly what the query was looking for with respect to defining the "Current Year" member as a string value. Here is the final code for reference:

Code: Select all

{FILTER(
    {FILTER(
       	{
          TM1SUBSETALL( [PERIOD MONTH MAIN] )},
          [PERIOD MONTH MAIN].[FORECAST MONTH] = "FORECAST")
       	},
     	RIGHT([PERIOD MONTH MAIN].[PERIOD MONTH MAIN].CURRENTMEMBER.NAME,2)=RIGHT([GLOBAL VARIABLE].([GLOBAL VARIABLE].[Current Year],[GLOBAL VARIABLE MEASURE].[String]),2)
     )}
Gtonkin, thank you so much for your support and guidance on helping me to solve this issue! I greatly appreciate it.

Thank you,
Michael
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: MDX Query Using Right Two Positions of a Global Variable

Post by gtonkin »

Pleasure - glad I could help.

If you have not seen the MDX guide, have a look at it or on Wim's site for some more tips.
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX Query Using Right Two Positions of a Global Variable

Post by MarenC »

Hi,

I have a similar requirement to the one outlined above.

However, I want to perform some maths and then return the value.

Basically I have a year from value I need to pick up, so for example, 2021/2022.

This value is taken from a cube. But I also want the year before, so I tried the following.

Code: Select all

FILTER( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Version].[All Versions]}, ALL, RECURSIVE )}, 0)},
 [Version].currentmember.properties('Start Fin Year') = 
 [Version Copy].([Version Copy Measures].[Year From], [General Measures].[String]) 
or 
 Left([Version].currentmember.properties('Start Fin Year'),4) = 
 Left([Version Copy].([Version Copy Measures].[Year From], [General Measures].[String]),4) -1 
 )
The minus 1 is simply to get the previous year.

I could not get anything to work so I added a new element in the Version Copy Measures, to reference the previous year.

But I was wondering if it was possible to do what I attempted?

Maren
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: MDX Query Using Right Two Positions of a Global Variable

Post by burnstripe »

Hi Maren,

The syntax failed because it's trying to perform a numeric operation -1 on a string. You'd need to convert the string to a number first which you can do using Val

Code: Select all

FILTER( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[Version].[All Versions]}, ALL, RECURSIVE )}, 0)},
 val([Version].currentmember.properties('Start Fin Year')) = 
 val([Version Copy].([Version Copy Measures].[Year From], [General Measures].[String])) 
or 
 val(Left([Version].currentmember.properties('Start Fin Year'),4)) = 
 val(Left([Version Copy].([Version Copy Measures].[Year From], [General Measures].[String]),4)) -1 
 )
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX Query Using Right Two Positions of a Global Variable

Post by MarenC »

Hi Burnstripe,

Yes that worked thanks greatly.

I was being silly, I tried VAL but got my brackets in the wrong place.

Maren
Post Reply