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: 
madmax88
Creator II
Creator II

Share of Total

Hi all,

I have a question. I have following table

   

CustomerProductValue
A110
A25
A310
B110
B20
B35
C10
C20
C310

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:

   

CustomerShare
A40%
B67%
C0%

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

22 Replies
madmax88
Creator II
Creator II
Author

Unfortunately Up&Download doen't work where i have my Qlik Sense. Maybe another Explanation helps to understand the Problem:

Given is following table:

   

CustomerContainerProductValue
Mad1AB5
Mad1AC10
Mad1AD20
Mad1AE55
Mad2AF10
Mad2AG5
Max3AB10
Max3AC10
Max3AG20
Max3AF50

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:

   

CustomerContainerProductValueShare in the Container
Mad1AB55.56%
Mad1AC1011.11%
Mad1AD2022.22%
Mad1AE5561.11%
Mad2AF1066.67%
Mad2AG533.33%
Max3AB1025.00%
Max3AC1025.00%
Max3AG2050.00%
Max4AF50100.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?

sunny_talwar

Got it, try this

Sum({$<criteria_1={'yes'}>}value)/Sum({$<criteria_1={'yes'}>}Product>} TOTAL <Customer, Container> Value)

madmax88
Creator II
Creator II
Author

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?

   

ProductValueShare in the Container
AB55.56%
AC1011.11%
AD2022.22%
AE5561.11%
AF1066.67%
AG533.33%
AB1025.00%
AC1025.00%
AG2050.00%
AF50100.00%
sunny_talwar

My bad, try this

Sum({$<criteria_1 = {'yes'}>} Value)/Sum({$<criteria_1 = {'yes'}, Product>} TOTAL <Customer, Container> Value)

sunny_talwar

Try this in that case

Aggr(Sum({$<criteria_1 = {'yes'}>} Value)/Sum({$<criteria_1 = {'yes'}, Product>} TOTAL <Customer, Container> Value), Customer, Container, Product)

madmax88
Creator II
Creator II
Author

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%

madmax88
Creator II
Creator II
Author

sorry, I used Aggr(Sum({$<criteria_1 = {'yes'}>} Product instead of Aggr(Sum({$<criteria_1 = {'yes'}>} Value

Now it works;)

Many thanks

madmax88
Creator II
Creator II
Author

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

sunny_talwar

Are you using expression default or not? If not, then please do

madmax88
Creator II
Creator II
Author

the color is for a chart. so color by key figure(with the different red color gradually). is this possible?