19 Replies Latest reply: Feb 25, 2014 6:26 AM by Manideep Hv

# Plotting Normal Dirstribution/ bell curve

Hi

I am trying to plot a Normal Distribution curve over the following barchart of sales increase.

Sales increase is a decimal representing the % increase.

I have tried a number of expressions but I'm having no luck,

I've tried using the NORMDIST function which I've played around but it isnt working, as well as creating an expression from scratch.

normdist(salesInc,avg(salesInc),stdev(salesInc))

for the graph I use the

dimension class(salesInc,5)

expression count(salesInc)

I'd be greatfull if you could point me in the right direction or show me some qlikview examples of normaldistribution,

• ###### Re: Plotting Normal Dirstribution/ bell curve

RIGHT CLIC PROPERTIES SORT, Y-VALUES DESCENDING.

HOPE IT HELPS

• ###### Re: Plotting Normal Dirstribution/ bell curve

Thanks Facundo,

I think the problem is more to do with my expressions for the normal distribution curve,as the Y axis and bar graph is how I would like it.

• ###### Re: Plotting Normal Dirstribution/ bell curve

can you add a qwv.?? to see real what happend?

• ###### Re: Plotting Normal Dirstribution/ bell curve

The normdist() function is cumulative, so I think you'll need to do something like this:

normdist(salesInc,avg(salesInc),stdev(salesInc))
-normdist(above(salesInc),avg(salesInc),stdev(salesInc))

I probably have something wrong there, but it's hard to know without a QVW to play with.

• ###### Re: Plotting Normal Dirstribution/ bell curve

Thanks John

I've played around with it and so far this formula seems to be producing a line on my graph i just need to see if its the right line.

I think my problem was with the class(salesInc, 10) function,I moved it from being a calculated dimension to being part of the expresssion

normdistCLASS(salesInc, 10) ,avg({1} total CLASS(salesInc, 10)),stdev({1} total CLASS(salesInc, 10)))
-normdist(above(CLASS(salesIncc, 10)),avg({1} total CLASS(salesInc, 10)),stdev({1} total CLASS(salesInc, 10)))) / (CLASS(salesInc,10)-above(CLASS(salesInc, 10))

• ###### Re: Plotting Normal Dirstribution/ bell curve

I found an example on my hard drive of calculating a normal distribution curve from points.

600*(normdist(      AHT ,avg({1} total AHT),stdev({1} total AHT))
-normdist(above(AHT),avg({1} total AHT),stdev({1} total AHT)))
/(AHT-above(AHT))

On brief glance, it looks like what you're doing other than the 600 part and your use of class().  So I suspect you have it working right.  As a possible simplification, rather than use class() in the expression or dimension, you could create a salesIncClass field in the script.

• ###### Re: Plotting Normal Dirstribution/ bell curve

There is in fact a fourth argument you can use with the normdist() function (it's not in the HELP, but in the editor tooltip), where you can switch between cumulative and non cumulative mode, so

normdistCLASS(salesInc, 10) ,avg({1} total CLASS(salesInc, 10)),stdev({1} totalCLASS(salesInc, 10)),0 )

should be enough.

• ###### Re: Plotting Normal Dirstribution/ bell curve

Hi johnw

I have the same requirement on the Bell Curve. After a lot of research I could find that your answer is nearer to what I actually need. I did not understood that in the above post why you are multiplying the expression with 600. I did not understood that. Can you please explain me that?

Regards

Manideep

• ###### Re: Plotting Normal Dirstribution/ bell curve

Hi,

I the formula above works to show the curve but does not work when the variables are grouped together, I have tried to group them together in the load script but it doesnt work as the x axis needs to be a number.

At the moment the x-axis is continuous numbers so the bars are counded for each individual number rather than a group.

Can anyone help me group the x axis?

• ###### Re: Plotting Normal Dirstribution/ bell curve

when I view my chart in table format these are the values that I have for some reason there is an error calculating the values around he mean

 Frequency Normal Frequency Normal Frequency -20 <= x < -15 1 0.000338372 - -15 <= x < -10 1 0.009322085 0.028161867 -10 <= x < -5 6 - - -5 <= x < 0 28 - - 0 <= x < 5 86 - - 5 <= x < 10 29 - - 10 <= x < 15 5 - - 15 <= x < 20 3 - - 20 <= x < 25 1 0.002968246 - 25 <= x < 30 1 0.000227747 0.007159788 30 <= x < 35 1 1.6E-5 0.000422699 40 <= x < 45 1 1.5E-9 2.7E-5 45 <= x < 50 1 1.4E-10 1.6E-9 - 20 - -

The first Column is the dimension  - class(salesInc,5)

2nd colomn expression - expression count(distinct salesId)

3rd column -normdist( salesInc,avg({1} total salesInc),stdev({1} total salesInc),0 )

4th comumn -   (normdist( salesInc ,avg({1} total salesInc),stdev({1} total salesInc))
-
normdist(above(salesInc),avg({1} total salesInc),stdev({1} total salesInc)))

* Count(DISTINCT

salesId))
• ###### Re: Plotting Normal Dirstribution/ bell curve

That's probably because salesInc is ambiguous for all classes with a frequency higher than 1.

If you want a sampling point in the center of your class width, try maybe

(floor(salesInc,5)+ceil(salesInc, 5))/2

like

normdist( (floor(salesInc,5)+ceil(salesInc, 5))/2,avg({1} total salesInc),stdev({1} total salesInc),0 )

• ###### Re: Plotting Normal Dirstribution/ bell curve

Thanks for the suggestion but still no luck

These are the figures that the expression is producing

 0.0008 0.0051 - - - - - - 0.0012 0.0001 0.0000 0.0000 0.0000 -
• ###### Re: Plotting Normal Dirstribution/ bell curve

Ah, sure, the same problem will persist using just floor() or ceil() function. Hm, maybe:

normdist( (floor(min(salesInc),5)+ceil(min(salesInc), 5))/2,avg({1} total salesInc),stdev({1} total salesInc),0 )

• ###### Re: Plotting Normal Dirstribution/ bell curve

This is the answer I'm getting, it is generating an answer for all the ranges now, however the numbers dont look like how I would expect

 Frequency -20 <= x < -15 1 8.5E-5 -15 <= x < -10 1 0.051645999 -10 <= x < -5 6 0.250697235 -5 <= x < 0 28 0.526009038 0 <= x < 5 86 3.593327034 5 <= x < 10 29 1.211703302 10 <= x < 15 5 0.230117249 15 <= x < 20 3 0.168979575 20 <= x < 25 1 4.1E-16 25 <= x < 30 1 0.006841735 30 <= x < 35 1 9.2E-6 40 <= x < 45 1 1.4E-10 45 <= x < 50 1 1.4E-10 - 20 -
• ###### Re: Plotting Normal Dirstribution/ bell curve

You are getting numbers larger that 1 using only the normdist() function?

What numbers do you expect?

• ###### Re: Plotting Normal Dirstribution/ bell curve

For a normal distribution curve the numbers should start off small, get bigger(around the mean) and get small again to create a bell curve.

Where as for the first range the number is 8.5, when it should be smaller than the result for the second range.

• ###### Re: Plotting Normal Dirstribution/ bell curve

Again, I doubt that this is the outcome of just a normdist() function. What is the exact expression you are using?

For the first range, the number is not 8.5, but 8.5E-5, i.e. 0.000085.

• ###### Re: Plotting Normal Dirstribution/ bell curve

Thanks, I over looked that. but I expect the numbers to be higher, a closer value to those in the frequency column.

normdist( (floor(min(salesInc),5)+ceil(min(salesInc), 5))/2,avg({1} total salesInc),stdev({1} total salesInc),0 )

* Count(DISTINCT(SalesId))

for the frequency column I am using -

Count(DISTINCT(SalesId))

in excel if I was to do this I would perform (normdist-previousNormDist)*totalFrequency and it works.

• ###### Re: Plotting Normal Dirstribution/ bell curve

I think

Count(DISTINCT(SalesId))

is not the correct factor to norm your bell curve so it does match your numbers.

I think you need to take the total number of SalesId into account:

count(TOTAL DISTINCT SalesId)

Hm, what about your class width? I think you also need to correct for that (so try an additional factor 0.2 (or 5?)

I don't have anymore time today, but maybe you are on the right path already.