15 Replies Latest reply: May 22, 2017 6:53 AM by Sunny Talwar RSS

    Create a Calculated Pareto Dimension for a Pie Chart

    Phalgun Parvathaneni

      Hello all! I have a dataset that is as follows:

       

      Vendor       Sales    Type

      A              1300        C

      B              2200        N

      A              1400        N

      B              2100        N

      C              2500        C

      A              300          N

      C              900          C

      D              3200        N

      B              2000        C

      D              1500        C

      E              1100        N

      F              3500        C

      E              1500        C

      G              1400        N

      H              600          C

      H              900          N

      G             1200         C


      Now I would like to create a Calculated dimension to use in a Pie chart. The Pie chart should have the Dimensions as 'C' and 'N Top 80%'. To further break it down, I'd like to show the % of Sales coming under the Type C and % of Sales coming under the Top 80%(Pareto) Vendors in Type N. Can you please help me create a calculated dimension to achieve this? TIA!

        • Re: Create a Calculated Pareto Dimension for a Pie Chart
          Sunny Talwar

          Can you put down the result you expect to see from this data in a tabular form for us to understand your requirement?

            • Re: Create a Calculated Pareto Dimension for a Pie Chart
              Phalgun Parvathaneni

              Sure. Now what I'd like for the Pie chart to have is two partitions: Sales under Type 'C' and Sales coming from Top 80% Vendors in Type N. If we perform a Pareto analysis on the above dataset within Type N, we can see that the Vendors 'A','B' and 'D' fall into the Top 80% category. So the result should display as follows: 9200 Under Top 80% Type N

              Vendor Split: A:1700(1400+300),

              B:4300(2100+2200),

              D:3200) and 

              15000 under Type C:

              (Vendor Split:A:1300,

              B:2000,

              C:3400(2500+900),

              D:1500,

              E:1500,

              F:3500,

              G:1200,

              H:600).

                • Re: Create a Calculated Pareto Dimension for a Pie Chart
                  Sunny Talwar

                  Something like this?

                   

                  Capture.PNG

                   

                  Dimension

                  =ValueList(1, 2)

                   

                  Expression

                  Pick(ValueList(1, 2),

                  Sum(Aggr(If(Rangesum(Above(Sum({<Vendor, Type = {'N'}>} Sales)/Sum({<Vendor, Type = {'N'}>} TOTAL Sales),0,RowNo()))<=0.8, Sum({<Type = {'N'}>}Sales)), (Vendor,(=Sum({<Vendor, Type = {'N'}>} Sales),Desc)))),

                  Sum({<Type = {'C'}>}Sales))

                    • Re: Create a Calculated Pareto Dimension for a Pie Chart
                      Phalgun Parvathaneni

                      As usual you are correct! Could you please explain a little bit about the expression and the ValueList function or give the relevant links? Much much appreciated! Thanks a lot!

                      • Re: Create a Calculated Pareto Dimension for a Pie Chart
                        Phalgun Parvathaneni

                        Sorry for the trouble but let's say there's another field for the data as follows:

                        Vendor       Sales    Type    Type1

                        A              1300        C           M

                        B              2200        N           S

                        A              1400        N           M

                        B              2100        N           M

                        C              2500        C           S

                        A              300          N           M

                        C              900          C           M

                        D              3200        N           M

                        B              2000        C           S

                        D              1500        C           S

                        E              1100        N           S

                        F              3500        C           M

                        E              1500        C          M

                        G              1400        N          S

                        H              600          C         M

                        H              900          N         S

                        G             1200         C         M


                        Now I'd like to display the same pie chart above for the Sales of in Bottom 20% Pareto of Type1='M'. How should I modify the above expression?

                          • Re: Create a Calculated Pareto Dimension for a Pie Chart
                            Sunny Talwar

                            How can you have two fields with Type1 header?

                              • Re: Create a Calculated Pareto Dimension for a Pie Chart
                                Phalgun Parvathaneni

                                My bad! I've made the correction.Thanks!

                                  • Re: Create a Calculated Pareto Dimension for a Pie Chart
                                    Sunny Talwar

                                    So this would be the third pie?

                                      • Re: Create a Calculated Pareto Dimension for a Pie Chart
                                        Phalgun Parvathaneni

                                        No. I would like to display the same pie chart but this time only for the data which comes under Bottom 20% Pareto in Type1='M'. Thanks!

                                          • Re: Create a Calculated Pareto Dimension for a Pie Chart
                                            Sunny Talwar

                                            Would you be able to provide the expected output just like you did the last time.

                                              • Re: Create a Calculated Pareto Dimension for a Pie Chart
                                                Phalgun Parvathaneni

                                                1.I'd like to perform a Pareto analysis on the above dataset with Type1='M'. So the data set reduces to:

                                                Vendor       Sales    Type    Type1

                                                A              1300        C           M

                                                A              1400        N           M

                                                B              2100        N           M

                                                A              300          N           M

                                                C              900          C           M

                                                D              3200        N           M

                                                F              3500        C           M

                                                E              1500        C          M

                                                H              600          C         M

                                                G             1200         C         M

                                                If you perform Pareto analysis on this data set, you'll see that Vendors C,E,G and H fall under the Bottom 20% Category. So, our data set now will be:

                                                C              900          C           M

                                                E              1500        C          M

                                                H              600          C         M

                                                G             1200         C         M


                                                Now on this data set I'd like to show the above mentioned pie chart with split according to 'C' or 'Top 80% N' In this case, it should display 100% Under 'C'.



                                                  • Re: Create a Calculated Pareto Dimension for a Pie Chart
                                                    Sunny Talwar

                                                    Something like this

                                                     

                                                    Pick(ValueList(1, 2),

                                                    Sum(Aggr(If(Rangesum(Above(

                                                    Sum({<Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'N'}, Type1 = {'M'}>} Sales)/

                                                    Sum({<Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'N'}, Type1 = {'M'}>} TOTAL Sales), 0,RowNo())) <= 0.8,

                                                    Sum({<Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'N'}, Type1 = {'M'}>}Sales)),

                                                    (Vendor,(=Sum({<Vendor= {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'N'}, Type1 = {'M'}>} Sales),Desc)))),

                                                    Sum({<Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'C'}, Type1 = {'M'}>}Sales))

                                                      • Re: Create a Calculated Pareto Dimension for a Pie Chart
                                                        Phalgun Parvathaneni

                                                        Whoa! That looks so complicated! But it works I guess! Thanks a lot again! It's going to take a while for me to figure out how this expression works I guess! Thanks a lot Sunny! I have learnt something new and really useful again!

                                                          • Re: Create a Calculated Pareto Dimension for a Pie Chart
                                                            Sunny Talwar

                                                            I guess breaking it down might help...where I have repeated this set analysis multiple times

                                                             

                                                            {<Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'C'}, Type1 = {'M'}>}

                                                             

                                                            and the important part was that you only wanted vendors which were bottom 20% in Type1, which is this part

                                                             

                                                            Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}

                                                             

                                                            To see how this behave, create a table with Vendor as dimension and this expression

                                                            =Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc)))

                                                             

                                                            You will only see C,E,H,G have value greater than 0.