Mdx statement for zero suppression based on control cube

[ahameed]

Hello, I have a Customers dimension and I would like to create a subset on it to show All elements if a value in a 2 dimension control cube is false and show only customers that have data in the Customers cube if the value in the control cube says true.

In the end I am trying to do zero suppression switch using a combination of a Pax websheet and a PAW view. The Zero suppression switch is in the Pax websheet that will suppress or unsuppress the customers in the PAW view.

I have tried many combinations of MDX based on the MDX reference that found here but cant seem to get it right. I know that I need to use IIF but can figure of the "then" part of statement.

This what I have so far

STRTOSET("[Customers].[Customers].[" + IIF([Client Selections].(STRTOMEMBER("[}Clients].[" + UserName + "]"),[MD_Selection].[Suppression Selection]) = "False" , " 9000000" , [Client Selections].(STRTOMEMBER("[}Clients].[" + UserName + "]"),[MD_Selection].[Header])) + "].children")

The 9000000 is a code for Total Customers and I am trying to get all level 0 customers with that.

Also, if I am overcomplicating things and just might be, kindly suggest a better why to achieve what I am trying to do. Thank you in advance for your time.

[ardi]

Try something like this:

Code: Select all

{FILTER ( {TM1SUBSETALL( [Customer_Dim] )} , [zControlCube].([ControlElements].[Zero Suppression Flag],[zControlCube_m].[Value])=0 OR ( [zControlCube].([ControlElements].[Zero Suppression Flag],[zControlCube_m].[Value])= 1 AND [CustomerCube].([Dim1].[Total_Elem], [Dim2].[Total_Elem], [CustomerCube_m].[Amount]) <> 0 ) )}

[ahameed]

Thanks for response @ardi. I appreciate it. Does it matter if the suppression flag is string. Like a True or False ?

[Wim Gielis]

Yes. If it is string you would compare with ="1"

[ahameed]

Thank you Wim.
I have this so far and the suppression filter is not working. Its probably the Or/And combo but I cant pin point it. When I select False in the Client Selections cube I do not get all the level zero elements.

FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Customers]) , 0) ,
[Client Selections].(STRTOMEMBER("[}Clients].[" + USERNAME + "]"),[MD_Selection].[Suppression Selection]) = "False"
OR [Client Selections].(STRTOMEMBER("[}Clients].[" + USERNAME + "]"),[MD_Selection].[Suppression Selection]) = "True"
AND
[Customer Summary].([Organizations].CurrentMember,[Periods].CurrentMember,[Regions].[99999],[Currency].[USD],[Customer Sources].[Total],[Versions].[Actual],[Customer Summary Measures].[SRP-Month-Sales]) >0)

[ardi]

You need to use round brackets after the OR operator

[ardi]

Try the following:


FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Customers]) , 0) ,
[Client Selections].(STRTOMEMBER("[}Clients].[" + USERNAME + "]"),[MD_Selection].[Suppression Selection]) = "False"
OR
([Client Selections].(STRTOMEMBER("[}Clients].[" + USERNAME + "]"),[MD_Selection].[Suppression Selection]) = "True"
AND
[Customer Summary].([Organizations].CurrentMember,[Periods].CurrentMember,[Regions].[99999],[Currency].[USD],[Customer Sources].[Total],[Versions].[Actual],[Customer Summary Measures].[SRP-Month-Sales]) >0 ) )

[ahameed]

That worked beautifully. Thanks for pointing out the fact that I failed to encapsulate the second clause. Thank you Ardian. Much appreciated.

[ahameed]

One last question and I promise this would be it. I think

Is there a way concatenate YTD to the [Periods].CurrentMember.

[Customer Summary].([Organizations].CurrentMember,[Periods].CurrentMember,[Regions].[99999],[Currency].[USD],[Customer Sources].[Total],[Versions].[Actual],[Customer Summary Measures].[SRP-Month-Sales]) >0 ) )

I tried [Periods].CurrentMember + ("YTD") but not resolving

[Wim Gielis]

Try the StrToMember() syntax with inside that [Periods].CurrentMember.Properties(“Name”) + “YTD” bit.

[ahameed]

Hey guys, I am back with more problems . I cant get that currentmember to resolve let alone add YTD to it. Any ideas on this one ?

FILTER(
TM1FILTERBYLEVEL(TM1SUBSETALL([Customers]), 0),
(
(
[Client Selections].(
STRTOMEMBER("[}Clients].[" + USERNAME + "]"),
[MD_Selection].[Suppression Selection]
) = "False"
)
OR
(
[Client Selections].(
STRTOMEMBER("[}Clients].[" + USERNAME + "]"),
[MD_Selection].[Suppression Selection]
) = "True"
AND
[Customer Summary].(
[Organizations].CURRENTMEMBER,
STRTOMEMBER("[PERIODS].["+ [Periods].CURRENTMEMBER.Properties("PeriodName") + "]"),
[Regions].[99999],
[Currency].[USD],
[Customer Sources].[Total],
[Versions].[Actual],
[Customer Summary Measures].[SRP-YTD-Sales]
) > 0
)
)
)

[ahameed]

Hi guys, I finally had the time to get back to this and getting close I think
I have come up with this but not getting any elements. Can someone point out what's wrong with my syntax. Thank you

STRTOMEMBER("[Periods].[" + [Periods].CURRENTMEMBER.NAME + " YTD]"),


FILTER(
TM1FILTERBYLEVEL(TM1SUBSETALL([Customers]), 0),

(
[Client Selections].(
STRTOMEMBER("[}Clients].[" + USERNAME + "]"),
[MD_Selection].[Suppression Selection]
) = "False"
)
OR
(
[Client Selections].(
STRTOMEMBER("[}Clients].[" + USERNAME + "]"),
[MD_Selection].[Suppression Selection]
) = "True"
AND
[Customer Summary].(
[Organizations].CURRENTMEMBER,
STRTOMEMBER("[Periods].[" + [Periods].CURRENTMEMBER.NAME + " YTD]"),
[Regions].[99999],
[Currency].[USD],
[Customer Sources].[Total],
[Versions].[Actual],
[Customer Summary Measures].[SRP-YTD-Sales]
) > 0
)
)

[MarenC]

Hi,

I think an alternative to this:

STRTOMEMBER("[Periods].[" + [Periods].CURRENTMEMBER.NAME + " YTD]")

would be to reference a lookup cube for the current period, so something along the lines of:

Code: Select all

StrToMember("[Periods].[" + [Lookup Cube].([Lookup Line].[Current Period],[Lookup Measure].[String] +" YTD]")

Maren

[ahameed]

Great idea @Marenc. That worked. Thank you so much.