Group by Margin Rate

[Anonymous]

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

[rwunderlich]

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

[Anonymous]

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 -

[robert_mika]

Can you post data that will match your outcome?

[Anonymous]

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

[rwunderlich]

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

[Anonymous]

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

[rwunderlich]

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

[Anonymous]

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.

[rwunderlich · Accepted solution]

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 Equal Sign

-Rob