Page 1 of 1
MDX Query Using Right Two Positions of a Global Variable
Posted: Thu Sep 01, 2022 1:39 pm
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
Re: MDX Query Using Right Two Positions of a Global Variable
Posted: Thu Sep 01, 2022 2:21 pm
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.
Re: MDX Query Using Right Two Positions of a Global Variable
Posted: Thu Sep 01, 2022 3:09 pm
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)
)}
Re: MDX Query Using Right Two Positions of a Global Variable
Posted: Thu Sep 01, 2022 3:17 pm
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
Re: MDX Query Using Right Two Positions of a Global Variable
Posted: Thu Sep 01, 2022 3:21 pm
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.
Re: MDX Query Using Right Two Positions of a Global Variable
Posted: Tue Sep 06, 2022 7:58 am
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
Re: MDX Query Using Right Two Positions of a Global Variable
Posted: Tue Sep 06, 2022 8:36 am
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
)
Re: MDX Query Using Right Two Positions of a Global Variable
Posted: Tue Sep 06, 2022 9:01 am
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