Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a question. I have following table
Customer | Product | Value |
A | 1 | 10 |
A | 2 | 5 |
A | 3 | 10 |
B | 1 | 10 |
B | 2 | 0 |
B | 3 | 5 |
C | 1 | 0 |
C | 2 | 0 |
C | 3 | 10 |
Goal is to find out the share of a product in a Portfolio of a customer. If I select Product 1, I want to know the share of product 1 to all products of each customer. In this case it would give me following results:
Dimension Selected: Product 1:
Customer | Share |
A | 40% |
B | 67% |
C | 0% |
How should be the expression in set Analysis?
With my Expression I get only the share of a selected product to all products for each customer:
sum({$<criteria_1={'yes'}>}Value)/sum({$<criteria_1={'yes'}>}Total <Customer> Value)
In this Case I would become this:
A 50%
B 50%
C 0%
Thanks
Unfortunately Up&Download doen't work where i have my Qlik Sense. Maybe another Explanation helps to understand the Problem:
Given is following table:
Customer | Container | Product | Value |
Mad | 1 | AB | 5 |
Mad | 1 | AC | 10 |
Mad | 1 | AD | 20 |
Mad | 1 | AE | 55 |
Mad | 2 | AF | 10 |
Mad | 2 | AG | 5 |
Max | 3 | AB | 10 |
Max | 3 | AC | 10 |
Max | 3 | AG | 20 |
Max | 3 | AF | 50 |
I have 2 customer. Each customer can have one or more Container, but each Container can only owned by one customer. In the case above customer Mad has 2 Containers (1 and 2) and Max has one (3).
In each Container there are products. E.g. Container 1 has 4 different products.
But the same products could be in different Containers. E.g. Product AC is in Container 3 (Max) and Container 1(Mad), but with different values.
Now I want to know the share of value for each product in a Container. Means: Product AB for Mad has a share of 5.56% (5/sum(5+10+20+55)). The result should be the following:
Customer | Container | Product | Value | Share in the Container |
Mad | 1 | AB | 5 | 5.56% |
Mad | 1 | AC | 10 | 11.11% |
Mad | 1 | AD | 20 | 22.22% |
Mad | 1 | AE | 55 | 61.11% |
Mad | 2 | AF | 10 | 66.67% |
Mad | 2 | AG | 5 | 33.33% |
Max | 3 | AB | 10 | 25.00% |
Max | 3 | AC | 10 | 25.00% |
Max | 3 | AG | 20 | 50.00% |
Max | 4 | AF | 50 | 100.00% |
Now what Kind of Expression can calculate the share of a product within a Container of a customer.
IF I take the Expression:
sum({$<criteria_1={'yes'}>}value)
/sum({$<criteria_1={'yes'}>}Product>} Total<Customer> Value)
I get the total Value of all Container, but I only Need the share of a product in his Container.
Does it help to understand?
Got it, try this
Sum({$<criteria_1={'yes'}>}value)/Sum({$<criteria_1={'yes'}>}Product>} TOTAL <Customer, Container> Value)
Ok, it works whenI have the table with the Customer Name as 2nd Dimension
But if I have only the Products (as only Dimension) in the table as:
then it doesn't work. Any idea how this would be possible?
Product | Value | Share in the Container |
AB | 5 | 5.56% |
AC | 10 | 11.11% |
AD | 20 | 22.22% |
AE | 55 | 61.11% |
AF | 10 | 66.67% |
AG | 5 | 33.33% |
AB | 10 | 25.00% |
AC | 10 | 25.00% |
AG | 20 | 50.00% |
AF | 50 | 100.00% |
My bad, try this
Sum({$<criteria_1 = {'yes'}>} Value)/Sum({$<criteria_1 = {'yes'}, Product>} TOTAL <Customer, Container> Value)
Try this in that case
Aggr(Sum({$<criteria_1 = {'yes'}>} Value)/Sum({$<criteria_1 = {'yes'}, Product>} TOTAL <Customer, Container> Value), Customer, Container, Product)
Aggr(Sum({$<criteria_1 = {'yes'}>} Value)/Sum({$<criteria_1 = {'yes'}, Product>} TOTAL <Customer, Container> Value), Customer, Container, Product)
With this Expression each product has a share of 0%
sorry, I used Aggr(Sum({$<criteria_1 = {'yes'}>} Product instead of Aggr(Sum({$<criteria_1 = {'yes'}>} Value
Now it works;)
Many thanks
Sorry, other question: do you know why the num function doesnt work in the expression
Aggr(Sum({$<criteria_1 = {'yes'}>} Value)/Sum({$<criteria_1 = {'yes'}, Product>} TOTAL <Customer, Container> Value), Customer, Container, Product)
i tried: num(Aggr(Sum({$<criteria_1 = {'yes'}>} Value)/Sum({$<criteria_1 = {'yes'}, Product>} TOTAL <Customer, Container> Value), Customer, Container, Product),'#.##%')
but it doesn't works..
Are you using expression default or not? If not, then please do
the color is for a chart. so color by key figure(with the different red color gradually). is this possible?