Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

10 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable
Author

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
Master III
Master III

Can you post data that will match your outcome?

Anonymous
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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