Hi,
I need to calculate the average shares of a product based on the average shares of its sales among customers in a given month. I have a pivot table in qlik sense like this
| |
01.06 |
01.05 |
| |
customer_1 |
customer_2 |
customer_1 |
customer_2 |
| product_1 |
0,6 |
0,3 |
0,1 |
0,8 |
| product_2 |
0,4 |
0,7 |
0,9 |
0,8 |
and a need add other measure, like this:
| |
01.06 |
01.05 |
01.06 |
01.05 |
| |
customer_1 |
customer_2 |
customer_1 |
customer_2 |
|
|
|
|
| product_1 |
0,6 |
0,3 |
0,1 |
0,8 |
sum(0.6+0.3)/ count(distinct customer) |
| product_2 |
0,4 |
0,7 |
0,9 |
0,8 |
sum(0.4+0.7)count(distinct customer) |
I need to have this indicator for each product in each month, according to the selected customers in the filter, because I want to compare this average with the average of a specific customer. However, when I do this using variables and add the sum of these variables to the table, it doesn't display anything.
and my variables looks like:
var1=Sum({<customer={'customer_1'}>} amount)/AGGR({1} nodistinct SUM(if(customer='customer_1', amount,0)), customer, date_month)
var2=Sum({<customer={'customer_2'}>} amount)/AGGR({1} nodistinct SUM(if(customer='customer_2', amount,0)), customer, date_month)
