Planning Analytics

Hi,

What is the data type of the re-entry date element? It a string or n type formatted as a date?

regards,

Mark

It's a string element, I have the same issue with the State element. I've worked around the issue creating a clone cube with the same dimensions except for the measurements, where I have copied the elements.

Same MDX structure, different result.

Thanks, 

Andrea 

HI Andrea, I think your issue relates to trying to filter one dimension where the view has two on the rows.

I think a better approach would be to update the MDX of the view itself to then filter on the combination of OFFERTE and the other dimension.

In my basic sales model where I may have comments against a combination of Customer and Product, I would want to filter on the contents of the cell that is related to both elements that make the combination.

Assuming I want to filter for a Customer/Product combination to show rows where the Comment field contains a particular string, I could use the following MDX:

SELECT 
   {
      [Sales Measures].[Sales Measures].[Units],
      [Sales Measures].[Sales Measures].[Comments]
   } ON 0, 
   { FILTER(
         { DISTINCT( {
                  TM1SubsetToSet([Customer].[Customer],"Leaf Elements","public")
               }*{
                  [Product].[Product].[Total Products^700201]
               })
         }, INSTR(1 , 
            [Sales Measures].[Sales Measures].[Comments] , 
            [User Preferences].(
              STRTOMEMBER("[}Clients].[" + UserName + "]"),
              [User Preferences].[Free text search]) , 1) > 0)  } ON 1 
FROM [Sales] 
WHERE (
   [Scenario].[Scenario].[Actual], 
   [Period].[Period].[2024-JAN])

If I then update the value of my Free test search field in my User Preferences cube to "caps" I get the following:

Based on your case, you may want to switch the parameters of the INSTR to suit your search string and your substring being searched for.

You may also need to filter blanks etc. but hopefully this gets you closer...

Good morning George, 

I think you got the issue. In the clone cube I have created I've dropped one of the dimension keeping just the dimension that I have to filter and the measure dimension, since I had problems also related in ordering the elements on some measures.

I've used also the hidden parameter of the Instr function and it works wonderfully. What I don't understand is why on the measure country (that I don't show in the example) and other text measure, the mdx on the original cube works perfectly.

The subset is subset is used in a websheet created with perspective, so I don't think the mdx views are supported. 

Thanks everybody for your replies and your help, and I'd like to thank you George for your MDX guides, they are illuminating.

Have a great day,

Andrea Bonelli 

HI Andrea, Glad that the MDX blogs are helping.

What you could try is applying the same MDX to both dimensions on the rows with changes to cater to addressing the areas based on the dimension you are filtering.

This should filter both but is still not a cross-joined filter i.e where the combination of OFFERTE and OPPORTUNITA (other dim) match the value.

You are still likely to get incorrect combinations purely because the elements in each dimension match at some point.

I will think about better ways to do this but see if the above gets you anywhere.