TM1 Forum

Hi, Yes I see this too. I can't imagine a situation where I would allow a non existent element to be included in the MDX but things like this does give cause for caution when using MDX! The only way I can get the statement to error is if I take off the dimension prefix, so instead of [Time].[2020_17...

Hi,

How about:
regards,
Mark

One of those occasions where you swear you are going mad!

My money would have been on cell B5, out of interest how exactly was that cell formatted, the cell that held Jan-2010?

regards,

Mark

Hi, I can't think of an mdx workaround. If these forms are NOT being consumed in TM1 web you could insert a column to DBRW to the category lookup cube and apply custom validation (Unfortunately custom validation does not appear to work in TM1web) on whether the DBRW returns Y, so only allow none zer...

Hi, It would be nice to know which dimensions are in your 2 .lookup cubes. Just looking at your category mdx statement (the second one), there is no reference to the category dimension in the mdx? It would appear to be pointing at the KPI dimension? Incidentally: I do not think what you are trying t...

Hi, I managed to get this work in a fashion, i.e. to asciioutput where the FTE = 0 and the Bonus Rate > 0. The problem comes with the string element, i.e. Action element. Under no circumstances could I get it to ascii where FTE = 0 and Action <>"" Incidentally I found the following to be m...

Hi,

So you have the dimensions category, company, department, division, employee and measure in your cube
and your MDX refers to only the employee and measures dimension? Also your Action measure is a string.

I am surprised the MDX returns the correct values under those circumstances.

regards,
Mark

Hi Folks, In the grand old days of perspectives I was able to build an active form with the option to do a wildcard search, to do this I ensured the alias was populated in the TM1RPTROW and then the mdx part of the TM1RPTROW would reference a cell. The mdx would be something like: ="{TM1Sort(TM...

Hi James,

no worries, tbh without planningdev I wouldn't have got there either!

Just for my sanity, what were the tweaks you made?

regards,
mark

Sorry misread that! You want to get the attribute value for Oct-2019! How about? FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Periods-Months])}, 0)}, [Periods-Months].[TM1 Last Day Serial Date] >= StrToMember("[Periods-Months].[" +[Global Variables].([Global Values].[Value],[Global Variables].[...

Hi Tomok,

it would be easier if the global value was numeric, but how about:

FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Periods-Months])}, 0)}, [Periods-Months].[TM1 Last Day Serial Date] >= strtovalue([Global Variables].([Global Values].[Value],[Global Variables].[LACT])))

regards, mark

Ha! Thanks PlanningDev, I get it. I think you are correct about the and statements, but I tried the following and it seemed to work: StrToSet( IIF(Instr(1, [GLOBAL Level].CurrentMember.Name, 'All Levels', 1) >0 or Instr(1, [GLOBAL Mode].CurrentMember.Name, 'All Modes',1) >0, "{TM1SubsetAll([Cou...

Hi PlanningDev,

Maybe you can explain where in the MDX Mode=PT AND level=LevelA is referenced because it is confusing me!

I don't think gTonkins long hand method will work here given I think this is spreading the same value to multiple cells, so if my understanding is correct the problem looks as follows: Source Cube: 2019 AreaA SectionD 100 2019 AreaA SectionE 200 Destination Cube: 2019 AreaA SectionD SubSectA 100 201...

Hi, I am slightly confused, which may well be me! What does the cube have anything to do with this, that is what I am struggling with. When you say Global Level and Global Mode are both title dimensions in this cube, which cube? And in reality Global Level and Global Mode are both just dimensions ar...

Hi, I haven't quite worked out what Global Level or Mode is, a subset, a single element in a dimension of one element? I don't think the UNION is the issue personally. Have you tried doing something like just to check it returns something: {UNION( StrToSet( IIF( [GLOBAL Level].CurrentMember.Name<>&q...

It would also help if example 1 had the correct number of parenthesis!

Every developer will hate me for saying this, I hate me for saying this, but good quality documentation and audit of any changes would, in theory, reduce the need to have to compare both environments. So if you document all changes you should just have to refer to the document to see where dev and p...

Hi,

no progress yet, got as far as you really. Have parked this at the mo due to other priorities but will be back on it at some point.

I will let you know how I get on!

regards, Mark

Hi gtonkin,

I noticed your steps were for 10.2, before I look into this, do you think all those steps are still relevant for planning analytics?

regards, Mark