4 Replies Latest reply: Aug 27, 2018 2:50 AM by Andrey Krylov RSS

    Clarification on set analysis use of P() for current dimension value

    Dan Dalnekoff

      Hello community!

       

      I've gotten myself stuck with set analysis - I think in my use case I need to use the P(), but it seems like I am just not quite grasping how to accomplish it.

       

      Overview - In my data I have what is basically a list of what customers have purchased.  I would like to summarize this by displaying how much customers have spent (in total, and avg per customer) on a given type of product (for example, Milk and Cheese = "Dairy") when they have purchased product ABC (ABC is technically any of the values for the dimension product).

       

      In my attached file, I have a sample of the data, and an idea on what I am trying to accomplish...it is roughly something like this:

       

      Data (Purchases)

      CustomerProductType of ProductAmount Spent
      AMilkDairy10
      APotatoesProduce25
      ACheeseDairy10
      BMilkDairy15
      BOrangesProduce35

       

      Desired output:

      Note: the way I have been thinking about this is that this is a table visualization, with the Product column populated from the dimension "Product" from my sample data, and the "For customers who bought this..." columns, I am writing a set expression for the Types I want to include that is something like this:

      sum( {< [Product] =, [Type of Product] = [<Value of desired type of product, ie/ Dairy>]  >} Amount Spent)

       

      It feels like I need something like:

       

      sum( {< [Product] =, [Type of Product] = [<Value of desired type of product, ie/ Dairy>], Customer = p( {<  [Type of Product] = [<Value of desired type of product, ie/ Dairy>] >} Customer)   >} Amount Spent)

       

       

      ProductFor customers who bought this product, how much did they spend on Dairy?For customers who bought this product, how much did they spend on Produce?
      Milk10+10+1525+35
      Oranges10+10+1535
      Cheese10+1025

       

       

      For some reason, I can't quite get the results I want.  Any support would be appreciated!  My attached excel file has more detail/a more comprehensive example....