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

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

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....

• ###### Re: Clarification on set analysis use of P() for current dimension value

Hi,

I think it's correct with your desired output :

P() is not convenient because it's related with selection,

here is aggr used

regards

• ###### Re: Clarification on set analysis use of P() for current dimension value

Hi Dan, you could use this expression.
Once for each Type

example:

IF(sum({\$<Type ={'Dairy'} >} [Amount Spent]) > '0',SUM({<Type = {'Dairy'}>}[Amount Spent]),0)

IF(sum({\$<Type ={'Bakery'} >} [Amount Spent]) > '0',SUM({<Type = {'Bakery'}>}[Amount Spent]),0)

• ###### Re: Clarification on set analysis use of P() for current dimension value

Olivier, Pablo - thank you both for your replies.  Both of your solutions did correctly solve the example I provided, and these were helpful solutions in achieving my goal.  My exact scenario was a bit more complex than I had the example (as I realized while working through it) to be, but I was able to leverage what you outlined to solve my challenge.

thanks again,

DD

• ###### Re: Clarification on set analysis use of P() for current dimension value

Hi, Dan, try this expression for Dairy

Sum(Aggr(Sum(TOTAL < Customer > Aggr(Sum({< Type = {'Dairy'} >} [Amount Spent]), Customer) ), Product, Customer, Type))