22 Replies Latest reply: Apr 19, 2018 11:52 AM by Sunny Talwar RSS

    Share of Total

    Max Maudin

      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

        • Re: Share of Total
          Sunny Talwar

          May be this

           

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

            • Re: Share of Total
              Max Maudin

              perfect, thanks!

              Will try in the next days and let you know if it works

                • Re: Share of Total
                  Sunny Talwar

                  Sounds good

                    • Re: Share of Total
                      Max Maudin

                      Short Update:

                      The Expression in a table seems to work:

                         

                      Product CategoryProduct SubcategoryProductCustomerValueShare
                      XAQ12057%
                      XAW11029%
                      YBE1514%
                      XAQ2525%
                      XAW2525%
                      YBE21050%

                       

                      Now I have an additional Value (Performance by customer). In the table it is correct (Same Performance for each product by customer-->I only have the Performance by customer and not by product).

                      Now I Need a Point Diagramm (Dimension Product, X-Axis "Share" and Y-Axis "Performance by customer"). If I select a random customer the Point Plot is correct. But If i want to Show the Plot for all customer (no selection), the X-Axis ("share") is wrong. How is it possible?

                        • Re: Share of Total
                          Sunny Talwar

                          Would you be able to share a sample to show what you have?

                            • Re: Share of Total
                              Max Maudin

                              Example should be the table above. Goal is a Point Plot with following expressions:

                               

                              Dimension: Product

                              X-Axis: Share: Sum({$<criteria_1={'yes'}>}Value)/sum({$<criteria_1={'yes'}, Product>}Total <Customer> Value)

                              Y-Axis: Performance: Median({$<Criteria_2={'yes'}>}Performance/100

                               

                              Now If I select Customer 1, the Point Plot Shows the Performance of Customer 1 and the Share of all Products. E.g. Performance is 1.5% and customer 1 has 3 products, then the plot Shows all 3 products by share (X-Axis) and each product has the same Performance of 1.5% (y-Axis).

                               

                              Now I de-select the customer and have again all Positions of all customers. There the share is wrong calculated.

                              Wrong means that the share is not calculated by each customer, but it calculates she share of each product to all products from all customer

                              Does it help you?

                                • Re: Share of Total
                                  Sunny Talwar

                                  I am not sure I am able to understand still... I am not even entirely sure if I will be able to recreate the chart with your issue and hence was asking you to share an app (may be with the above data) and recreate the chart and issue for me so that I can see and play around with it.

                                    • Re: Share of Total
                                      Max Maudin

                                      how can i share an example or screenshot?

                                      If it doesnt work: On the Point Chart it Shows me the share of Product over all customer and not the share of product in the Portfolio of a customer

                                          • Re: Share of Total
                                            Max Maudin

                                            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?