Select function with subsets?

I have a subset A which is a selection of the Global Customer List.


In my module which applies to the subset A, I'm trying to retrieve the Sales for the top level of the subset "total customers" (only the total of the selection of the customers from the global customer list). I tried [Select:'Subset A'.'total customers']. It is invalid.


I then changed it to [Select:'Global Customer'.'total customers'], it works. Unfortunately this is not the total customers number that I need.


It seems the Select function does not work with a subset? What would be the proper function to use then? Thank you!

Best Answer

  • jnoone
    Answer ✓

    Hey - I think the calculation just needs updating.  It looks like it should be:  master module.sales[sum: master module.customer].  That will map to the dimension in your target module (which is by semi-conductor customer) and the top level should not be relevant as it is directly mapping to the members.  


    Even better would be to house the master data properties in their own module separate from sales.  So the formula would look like: master module.sales[sum: master module properties.customer].   Also just wanted to confirm semi-conductor customers is a subset and not a property (as mentioned below)?  It sounds like subset is better in any case for what you're looking to achieve.  It's a little tough without screenshots so might be helpful to send along if this does not solve.  


  • Hey - Rather than using the select function can you try this: directly reference the line item you are trying to grab without the Select included.  By nature it should grab the top level and naturally should aggregate just the subset totals.  I did so using an example  in my model and it worked as expected.    

  • Not working in my case because the line item that I'm trying to grab, is a Sum function that looks up the customer.

  • @lilasququ

    I still don't understand what is stopping in getting the top level just by referring as @jnoone mentioned in the earlier thread. Is that line item's summary turned off? Can you please elaborate more here so that we could fig out your concern and provide a quick solution. Thanks!

  • The module is by Semi-conductor Customer for example. The line item sales needs to be shown by customer: So the formula references another master module to pull data by customer using a Sum function.  The formula is something like: 'Mater Module'.Sales[Sum:ProductCustomer.Semi-conductor Customer']. "ProductCustomer" is a List used in the Master Module. "Semi-conductor Customer" is a property in that list.


    Now I'm creating a new line item total Sales of semi-conductor customer. It should just grab the top of the list from this module. But when I just reference the Sales, it is always equal to it for every customer.



  • It's a little hard to talk through here. But I did get inspiration to find a workaround for now. I'm cherry picking the customers I need to get the total for now.