Hello guys, I have a table where I'm calculating the avg of sales, but depending on how that table is shown (I have a button that show/hide a column 'Market'), that avg should be market by market or the avg of all markets, but the qlikview should calculate the avg depending on which markets are common with the product who is selected as filter and the product who is listed in the table.
The table which the column market hidden (that column need to identify which markets are common and calculate the avg based on that) is something like this, you guys will notice that the avg(sales) of the product who is being filtered is the same for every product listed, but is should not be like that, I need to get the 4 markets who is common with Prod 1 and the product who I selected as a filter and calculate based on these 4 markets.
List of Products | Count of common markets | avg(sales) of the Product selected on filter | avg of product listed in the first
column (Prod 1, Prod 2...)
Prod 1 4 45 45
Prod 2 3 45 51
Prod 3 4 45 41
Prod 4 2 45 33
So basically the table above do the count() of common markets, calculates the avg of the product who is selected as a filter, remembering that this avg should be the avg on the same markets that the prod 1, prod 2 had sales.
And here is an example of the table with the analysis market by market beign shown (the column ''Count of common markets'' is replaced by ''Markets'', who is the column with detailed markets):
List of Products | Market | avg(sales) of the Product selected on filter | avg of product listed in the first
column (Prod 1, Prod 2...)
Prod 1 21 53 41
Prod 1 33 23 48
Prod 1 25 33 38
Prod 1 26 30 40
Prod 2 21 43 40
Prod 2 18 48 40
Prod 2 12 51 40
I have this who is calculating correctly the avg(sales) market by market, but when it comes to show the avg(sales) of all markets this formula doesn't work and show the same avg(sales) for everyone, which is not right.
Avg(TOTAL <Market> {<Product={"$(=Product2)"}>} Sales)
note: I have two fields Product who is my filter 1, and Product2 who is my filter 2, so I need the set analysis above comparing both
Sorry for the long post and I really hope that I'm being clear on what I need.
