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

# 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

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://robwunderlich.com

• ###### Re: Group by Margin Rate

Hi Rob

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

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

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

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

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

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

• ###### Re: Group by Margin Rate

Thanks Rob!!

Learnt a lot today - Thankyou for your help I really appreciated it!

Sagar