Hi Jenny,

This problem falls into the category that requires analysis / calculation from the underlying table of data that makes up your dimension / attributes. You would not be able to ascertain your selection criteria from the Hierarchy / Attribute as these items consist of a list of unique values from the table, rather than all the values, hence your lack of success!

Can you confirm what underlying database technology you are using? Is it SQL based (our IMDB or other SQL engine like SQLServer or Oracle) or are you using Analysis Services or SAP BW cubes ? If it is the latter, then there is no way for Pyramid to calculate this as we do not have access to the underling data that was used to build the cubes.

Thanks,

Ian

Thanks Jenny,

Yes, it should be. One more question, is your Status field in the same table as the Product Field? If not, how are the two related?

Ian

Hi Jenny,

In your hierarchies panel, right click on Product and choose "Aggregate / Count". Your menu may look different to mine as you will be on an earlier release, but you should have the "Count" function available somewhere.

Make the Measure shareable so that we can use it in Formulate:

Create a Discover view with Product and the new Count Measure. This will tell you how many times each product appears in the table.

I've also put a total on the top which shows how many records there are in this table.

Now we need to examine the number of times a product has Status A and how many times a product has Status B. In fact we want to know which Products have both Status A and Status B more than once each. In my example I wil use "Socialdiscount" which has values of Yes, No and NULL. I'll use Yes and No.

Create another new Measure using Formulate like this:

This is saying, If the count of Product with Status A (SocialDiscount, No) is greater than 1...

...AND the count of Product with Status B (SocialDiscount, Yes) is greater than 1 ,then set the Measure to 1 otherwise 0. When we place this Measure, Flag, on our grid above, those Products that have Status of A (SocialDiscount, No) and B (SocialDiscount, Yes) more than once each will show as 1. We can then filter on Flag to only show those products where my test is true:

So in my data, 157 products, listed, have a Status of A and B more than once each.

Hope that helps.

Ian