Mimic member logic within lists

Dave_Carbery
1 yr ago
6replies
Answered

Hello folks.

I have a very handy variable list object which filters a large number of products into the end-user's desired internal product categories. I would like to now compute the percentage represented of each component in that list relative to the maximum. My instinct is to try building some current member logic. However, as I am working with a list I cannot access the member functions. Is there a parallel approach I can examine to achieve this logic?

To help contextualize this Q, here is some dummy data. A bike company make cross, mountain, road and child bikes. The % of the total market is displayed for the 4 categories, e.g. Sales[Cross Bikes]/Sales[Total Market Bikes] = 7.2%.

Many thanks in advance, Dave

- Senior Director of Product
- Ian_Macdonald
- 1 yr ago
- Reported - view
Hi Dave,

You say you have a "variable list", but you cannot access the member functions. Can you share please how the list is created? What are the members of the list? How are they defined? Is this a List of members of a hierarchy?

If the latter, you can use the Currentmember function. Say your List includes the members as defined above. Select the list and put the host hierarchy on the rows, your sales in the columns then calculate your % Measure thus:

([Grouping].currentmember,[Measures].[Sales]) / Maximum(<list name>, [Measures].[Sales])

Hope that helps.

Ian
- Dave_Carbery
  
  1 yr ago
  
  Reported - view
  Ian Macdonald Hi Ian. OK, here goes with elaborating my initial Q.
  
  I have a column, product, with 450 product names. I have hand-built, via view elements in discovery, 45 individual lists consisting of groups of these product names. In turn, I have parameterized these 45 lists by building a switch controlled variable list so that end users can access individualized data regarding the product groups of interest.
  
  I am looking to compute for each individual product in the selected grouping what the percentage of that product is of the maximum value in that grouping. I have attempted to build current member logic but as the selected grouping from the variable list parameter dropdown returns a list (or so my error messages in Formulate tell me) I am unable to build anything using the member group of functions, e.g. currentmember().
  
  I can achieve this desired outcome of generating percentages of the max by using context calcs via the right click menu. However, I actually need to compute the YOY point change of these percentages. So, a 2nd question, is there any way to subsequently use context calculation output columns in a new calculation (i.e. the YOY point change).
  
  Thanks, Dave
- Senior Director of Product
- Ian_Macdonald
- 1 yr ago
- Reported - view
OK.

But your variable lists still contain Products, whose values are Members of the Product dimension, so currentmember is still available.

I've created 5 lists of Products here, products beginning with A, B, C, D and E. I've created a variable list so I can choose which List of products to display. Put Products on Rows and select the Variable List. Add Sales to columns then create this calculation:

Data Point is Product - CurrentMember and Sales Measure / Maximum of <select Variable List>, Measure, Sales. Save to Measures with format of 0.00%. Save as % Max and put on columns:

Hope that helps.

Ian
- Dave_Carbery
- 1 yr ago
- Reported - view
Hi Ian, many thanks for the reply, insight and suggested solution. I've gone and attempted your strategy.

Initially, I attempted to mirror your suggestion using the objects I had already built. However, I was seeing the same error message as before. So, I decided to quickly rebuild a new batch of lists (A->D for me, a bit more cut to the chase for myself) and in turn built a new variable list in discovery's variable list builder and in turn the syntax for the custom calculation. This has *largely* worked...I can see the correct logic being applied, i.e. 100% for largest sales and the correct percentages for smaller selling products. However, what I am finding is that for all of the 4 lists there is an incomplete return of products. Eg, in my B list I have 8 products pre run, but post run I see 5 outputted. This is the same for all of these lists.

So, 2 head scratchers...why did my original Variable List built in formulate not work? Incidentally, I had originally started building the original variable list in the Variable List wizard but found I couldn't add all ~45 lists. It was as if there was a space limit imposed behind the scenes.

And, where have some of my prods gone!

it's a funny old game
- Dave_Carbery
- 1 yr ago
- Reported - view
Ian, I've worked out one half of my problem, the missing data...this has resolved (or at least know why I'm missing the data. Thanks again, Dave
- Senior Director of Product
- Ian_Macdonald
- 1 yr ago
- Reported - view
Great! I'll mark this as answered then.

Ian