Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pedro_freire
Contributor III
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:


Thanks in advance for all your help


Pedro Freire

1 Solution

Accepted Solutions
sunny_talwar

You can try this:

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

to get this

Capture.PNG

D and E doesn't match.

D might be a typo from your end

Capture.PNG

Adds up to 4300. but your chart shows this

Capture.PNG

E seems to be because of duplicate values

Capture.PNG

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

Capture.PNG

View solution in original post

10 Replies
sunny_talwar

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

pedro_freire
Contributor III
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!

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

ramasaisaksoft

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

pedro_freire
Contributor III
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.

pedro_freire
Contributor III
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 =

   

#RowaProdCodeStoreSalesUnits
1101Lisboa1000
2102Espinho1200
6101Espinho1000
7102Espinho1200

____________________________________________________

Line 2 =

   

#RowaProdCodeStoreSalesUnits
2102Espinho1200
6101Espinho1000
7102Espinho1200

_____________________________________________________

Line 3 =

   

#RowaProdCodeStoreSalesUnits
3103Nazaré1300
8103Lisboa1300

_____________________________________________________

Line 4 =

   

#RowaProdCodeStoreSalesUnits
4104Cascais1400
9104Cascais1400
10105Cascais1500

_____________________________________________________

Line 5 =

   

#RowaProdCodeStoreSalesUnits
4104Cascais1400
5105Ericeira1500
9104Cascais1400
10105Cascais1500
sunny_talwar

You can try this:

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

to get this

Capture.PNG

D and E doesn't match.

D might be a typo from your end

Capture.PNG

Adds up to 4300. but your chart shows this

Capture.PNG

E seems to be because of duplicate values

Capture.PNG

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

Capture.PNG

pedro_freire
Contributor III
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!