10 Replies Latest reply: Feb 11, 2015 4:43 PM by Sagar Hussain RSS

    Group by Margin Rate

      Hi

       

      I have a set of information that has invoices as its main data - customer,invoice val, cost etc. fairly basic data.

       

      I am trying to create a report that shows all count of customers with a margin above 50%, 41%, 39% etc.

       

      How can I do this? Really appreciate any help with this...

       

      regards

        • Re: Group by Margin Rate
          Rob Wunderlich

          Do you want a chart?

           

          Create a Calculated DImension of:

           

          =aggr(

          Round(sum(Margin) / Sum(LineSalesAmount), .02)

          , Customer)

           

          The .02 above is rounding for grouping, you may want to use another value . And then an Expression of:

           

          =count(DISTINCT Customer)

           

           

          -Rob

          http://masterssummit.com

          http://robwunderlich.com

            • Re: Group by Margin Rate

              Hi Rob

               

              Thanks for your answer!

               

              The data I have looks like this:-

               

              Invoice No     Customer      product   cost   invoice val

              1                     A1            widget1     1.2      5.0

              2                     C1             widget2     1.4     4.8

              3                    B3            widget8       1.6     2.4  etc etc

               

              and I am trying to get:-

              Margin Class       Count       Sales Val    Gross Profit in val  Margin%

              Platinum                2            3.60             1.80                      54

              Gold                      1            2.80             2.2                        45

              Silver                    3             3.40             2.8                        34

              Bronze                 6              7.80            6.8                         28

              Lead                    3              6.60            6.0                         19

               

              (please ignore the numbers I just made them up...)

              I am trying to see the count of customers where we make 50% margin or over (called Platinum) Gold is between 40 and 50%, silver is 30 and 40% and so forth -

                • Re: Group by Margin Rate
                  Robert Mika

                  Can you post data that will match your outcome?

                    • Re: Group by Margin Rate

                      I dont unfortunatley dont have any real data that matches my outcome...

                       

                      All I can say is I have invoices that are in a table from which I calculate the margin - I then want to group the margin figures into five bands so that we can see which customers we earn good margin on and which we dont...

                        • Re: Group by Margin Rate
                          Rob Wunderlich

                          You can modified my suggested Dimension with if()

                           

                          =aggr(

                          if(sum(Margin) / Sum(LineSalesAmount)>=.54, 'Platinum'

                          ,if(sum(Margin) / Sum(LineSalesAmount)>=.45, 'Gold'

                          ,if(sum(Margin) / Sum(LineSalesAmount)>=.34, 'Silver'

                          ,'Unclassified'

                          )))

                          , Customer)


                          I'm sure you can see the pattern to add the other classes. If you don't want to put the remainder in 'Unclassified', just leave that off and let it default to null().


                          -Rob

                            • Re: Group by Margin Rate

                              I have tried this

                               

                              =aggr(

                              if(MarginCalc>=.54, 'Platinum'

                              ,if(MarginCalc>=.45, 'Gold'

                              ,if(MarginCalc>=.34, 'Silver'

                              ,if(MarginCalc<=.33, 'The Rest'

                              )))),null())

                               

                              I am using a straight table and it does not work... MarginCalc is a variable which is =(InvoiceVal-InvoiceCost)/InvoiceVal

                               

                              Nothing is returned...

                                • Re: Group by Margin Rate
                                  Rob Wunderlich

                                  First, you must aggr() over (the second parm to aggr)  the Dimension. You cannot aggr() over null().

                                   

                                  Second, you need to reference the variable with $().

                                  $(MarginCalc)

                                   

                                  -Rob

                                    • Re: Group by Margin Rate

                                      Hi Rob

                                       

                                      I did amend my report to reflect what you have said after posting my earlier response - but again I get no data coming back.

                                       

                                      I attach my file to this post - perhaps you can see what I am doing wrong - hopefully something will stand out like a sore thumb which I am obviously not seeing.

                                        • Re: Group by Margin Rate
                                          Rob Wunderlich

                                          Thanks for post a qvw. Makes it a lot easier to help. I've attached a corrected example. You had a few problems.

                                           

                                          1. Even though the expression editor said "Expression OK", you still had a missing paren in your dimension (as indicated by the red paren). The "Expression OK" is a QV bug. The expression was not OK.

                                          2. I moved "The Rest" to be your final else, to include the Customers with no Sales, which return null for your expression.

                                          3. Your chart expression was count(distinct Customer) but your field name is "customer" (lower case).

                                          4. Your variable definitions included "=" which meant they were calculated only once, instead of per row, I removed the "=" and also added the $() to the nested variable references. See The Little Equals Sign

                                           

                                          -Rob