Evaluate Chart Expression at line level

[pedro_freire]

Hello,

I’m trying to answer the following question using a Chart (Straight or Pivot Table) layout:

Considering that each product has one or more associated Markets , I want to know for each Product on the table, what is the total Sales Units on the product respective Markets but only on the Stores that have sold the product:

Something like this:

Product (dimension)	Sales Units (expression)
Prod_A	Total Sales_Units of the Prod_A's Market, on the stores that sold Prod_A
Prod_B	Total Sales_Units of the Prod_B's Market, on the stores that sold Prod_B
Prod_C	…

So far I can list all Market sales on each Product in the list, but I can’t make the filter for “only the stores that have sold the product” as I can´t manage to pass the Product Dimension through the aggr Total in expression.

I’m using the following expression so far:

=Sum(Aggr(SUM(TOTAL <Market> {<Market=P(Market)>} SalesUnits),Market,ProdName))

Please see attached a small example of the problem:

Thanks in advance for all your help

Pedro Freire

[sunny_talwar]

I am not 100% sure I understand your requirement. What is your expected output that you are hoping to see?

[pedro_freire]

Hi Sunny,

Imagine Prod_A is Baby Diaper XPTO on the Baby Diapers Market, and Prod_B is Baby Lotion XPTO on Shower & Bad Market, what i need is a chart like this:

Product (dimension)	Sales Units (expression)
Baby diaper XPTO	Total Sales_Units of all Baby Diapers (total selling of market in store), on the stores that sold Baby diaper XPTO
Baby Lotion XPTO	Total Sales_Units of all Shower & Bad products (total selling of market in store), on the stores that sold Baby Lotion XPTO
Prod_C	…

Hope that helps!

[ramasaisaksoft]

Hi Pedro,

Better to create text objects so that u can write your own expressions for each product and design just like a Straight table.

[pedro_freire]

Thank you Rama Sai, but that is not an option for me as I may have a few hundred Products in the dimension. The chart will be dinamicaly calculated uppon selections.

[ramasaisaksoft]

If u use pivot table with aggregated by product you will get this result.

[pedro_freire]

As i mentioned in the previouse attached example with a pivot table i can aggregate as far as the market I can have for each Product the  its Market's TOTAL sales units, but i cannot filter that result for only the stores that have soled the product.

[pedro_freire]

Following the attached example on the original post, this is the result table i would expect:

ProdName	Sales units
Prod_A	4400
Prod_B	3400
Prod_C	2600
Prod_D (reviewd)	4300
Prod_E	8800

Where:

Line 1 =

#Rowa	ProdCode	Store	SalesUnits
1	101	Lisboa	1000
2	102	Espinho	1200
6	101	Espinho	1000
7	102	Espinho	1200

____________________________________________________

Line 2 =

#Rowa	ProdCode	Store	SalesUnits
2	102	Espinho	1200
6	101	Espinho	1000
7	102	Espinho	1200

_____________________________________________________

Line 3 =

#Rowa	ProdCode	Store	SalesUnits
3	103	Nazaré	1300
8	103	Lisboa	1300

_____________________________________________________

Line 4 =

#Rowa	ProdCode	Store	SalesUnits
4	104	Cascais	1400
9	104	Cascais	1400
10	105	Cascais	1500

_____________________________________________________

Line 5 =

#Rowa	ProdCode	Store	SalesUnits
4	104	Cascais	1400
5	105	Ericeira	1500
9	104	Cascais	1400
10	105	Cascais	1500

[sunny_talwar · Accepted solution]

You can try this:

=Sum(Aggr(Sum(TOTAL <Store, Market> SalesUnits), Store, ProdName, Market))

to get this

D and E doesn't match.

D might be a typo from your end

Adds up to 4300. but your chart shows this

E seems to be because of duplicate values

My chart is showing 3000 more because of the #Rowa 5 and 10 getting double counted. If you don't want to double count them, you can try this:

=Sum(Aggr(Sum(TOTAL <Store, Market> Aggr(SalesUnits, #Rowa)), Store, ProdName, Market))

This one for some reason fixes the Prod_D also

[pedro_freire]

I think you got it!

D is in fact a typo on the table as the line detail is showing the resolt should be 4300. My bad

As for E, it has to be duplicated because one product can be in more than one market.

Thank for you help!