topic Re: Evaluate Chart Expression at line level in QlikView

Evaluate Chart Expression at line level

pedro_freire — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Evaluate Chart Expression at line level

sunny_talwar — Sun, 08 Jan 2017 23:32:40 GMT

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

Re: Evaluate Chart Expression at line level

pedro_freire — Mon, 09 Jan 2017 10:22:27 GMT

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!

Re: Evaluate Chart Expression at line level

ramasaisaksoft — Mon, 09 Jan 2017 10:28:47 GMT

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.

Re: Evaluate Chart Expression at line level

pedro_freire — Mon, 09 Jan 2017 10:35:01 GMT

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.

Re: Evaluate Chart Expression at line level

ramasaisaksoft — Mon, 09 Jan 2017 10:37:29 GMT

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

Re: Evaluate Chart Expression at line level

pedro_freire — Mon, 09 Jan 2017 10:46:02 GMT

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.

Re: Evaluate Chart Expression at line level

pedro_freire — Mon, 09 Jan 2017 11:34:34 GMT

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

Re: Evaluate Chart Expression at line level

sunny_talwar — Tue, 10 Jan 2017 04:52:10 GMT

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

Re: Evaluate Chart Expression at line level

pedro_freire — Tue, 10 Jan 2017 09:19:39 GMT

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!

Re: Evaluate Chart Expression at line level

pedro_freire — Sun, 19 Mar 2017 16:49:40 GMT

Hello guys and thank you again for all your help!

As for the solution Sunny presented, it work perfectly until we had to move up one "notch".

On the January problem, we had one table with a one-to-one relationship between productcode and market.

Now we have grouped Products and for each ProductGroup it's possible to have more than one Product and More than one Market.

The expected result shoul be somthing like this:

Like in January, the main objective is to to have a table where, for the stores that have soled each ProdCode we will have the Sum(SalesUnits) for the Sub-Markets (Categories) where this ProductCode belongs to.

Once again I thank you for your help!