Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Evaluate Chart Expression at line level

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:

Pedro Freire

1 Solution

Accepted Solutions
MVP

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

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

10 Replies
MVP

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

Contributor III
Author

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!

Master

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.

Contributor III
Author

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.

Master

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

Contributor III
Author

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.

Contributor III
Author

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
MVP

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

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

Contributor III
Author

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!

Community Browser