Two dimensions where not all cross joined combinations are needed




i have a list of divisions and a list of products. The divisions are defined by customer type and not by product, so not all divisions sell all products, but there is a lot of overlap. We would want to be able to show sales by division, and also sales by product.


The easy thing would be to have two unrelated lists, but the issue with that is the vast majority of combinations will always be blank. Also a division FC would want to see only those products relevant for his division, and not some giant list.


Illustrative example is below, but there would be a lot more “missing” products.


Division A

    Product W

    Product X

    Product Y


Division B

    Product X

    Product Z


Division C

    Product X

    Product Y

    Product Z


Any suggestions? TIA

Best Answer

  • LipChean_Soh
    Answer ✓



    Screen Shot 2019-06-22 at 3.33.17 PM.png

    There are 3 lists.

    'L2 Prod#' rolls up to 'L1 Division'. These 2 lists help keep sparsity low, since each Division sells specific Products.

    'Product Flat' is the list of available Products.

    Screen Shot 2019-06-22 at 3.35.05 PM.png

    Users can assign specific 'Product Flat' to the different 'L2 Prod#', and the name is reflected as the 'Display Name' of 'L2 Prod#' the numbered list.


    Screen Shot 2019-06-22 at 3.33.24 PM.png

    'L2 Prod# Sales' module is where you enter sales for different products sold under each Division.

    'Product Flat Sales' module is where the aggregation by Product happens. 

    Note the keyword SUM.


    Screen Shot 2019-06-22 at 3.33.38 PM.pngScreen Shot 2019-06-22 at 3.33.50 PM.pngScreen Shot 2019-06-22 at 3.33.56 PM.png





  • Hi,


    The issue you're facing is not uncommon, and you're right to recognize there's a better alternative to storing these unique combination of Division_Product.


    You should check out 'Numbered List' to help you achieve what you want.




  • @GaryPage ,


    Create a concatenated hierarchy with L1 being Divisions and L2 being Products and have the list built from the actual data or transactional data (course 305: Hierarchy Management).  L2 will be a numbered list with the Display Name property being list formatted to the Product Flat list. 


    Hope this helps,



  • @rob_marshall @LipChean_Soh Thanks both. 

    I can see how this approach will let me get a hierarchy will only have the required items, and that the numbered list with display names will get around the duplication of names. I am not sure how this will allow me to show a breakdown of sales by product. Even though the products will have the same name, they won't really be the same under the hood will they?


    I haven't looked at the 305 training yet, but I will do.