topic Re: Group by Margin Rate in QlikView

Group by Margin Rate

Anonymous — Mon, 09 Feb 2015 16:06:59 GMT

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

rwunderlich — Mon, 09 Feb 2015 17:06:41 GMT

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

Anonymous — Mon, 09 Feb 2015 21:47:50 GMT

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 — Mon, 09 Feb 2015 22:48:11 GMT

Can you post data that will match your outcome?

Re: Group by Margin Rate

Anonymous — Tue, 10 Feb 2015 23:28:41 GMT

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

rwunderlich — Tue, 10 Feb 2015 23:36:59 GMT

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

Anonymous — Wed, 11 Feb 2015 00:08:11 GMT

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

rwunderlich — Wed, 11 Feb 2015 01:14:10 GMT

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

Anonymous — Wed, 11 Feb 2015 08:34:03 GMT

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

rwunderlich — Wed, 11 Feb 2015 19:01:23 GMT

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

Re: Group by Margin Rate

Anonymous — Wed, 11 Feb 2015 21:43:01 GMT

Thanks Rob!!

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

Sagar