topic Re: Plotting Normal Dirstribution/ bell curve in QlikView

Plotting Normal Dirstribution/ bell curve

Mon, 15 Oct 2012 14:57:21 GMT

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

fdelacal — Mon, 15 Oct 2012 15:00:26 GMT

RIGHT CLIC PROPERTIES SORT, Y-VALUES DESCENDING.

HOPE IT HELPS

Re: Plotting Normal Dirstribution/ bell curve

Mon, 15 Oct 2012 15:15:11 GMT

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

fdelacal — Mon, 15 Oct 2012 15:21:37 GMT

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

Re: Plotting Normal Dirstribution/ bell curve

johnw — Mon, 15 Oct 2012 20:56:22 GMT

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

Tue, 16 Oct 2012 09:04:00 GMT

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

normdist( CLASS(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

johnw — Tue, 16 Oct 2012 18:16:42 GMT

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

swuehl — Tue, 16 Oct 2012 18:38:39 GMT

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

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

should be enough.

Re: Plotting Normal Dirstribution/ bell curve

Wed, 28 Nov 2012 10:35:31 GMT

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

Wed, 28 Nov 2012 11:48:35 GMT

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

swuehl — Wed, 28 Nov 2012 13:15:14 GMT

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

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

Re: Plotting Normal Dirstribution/ bell curve

Wed, 28 Nov 2012 14:05:27 GMT

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

swuehl — Wed, 28 Nov 2012 14:08:36 GMT

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

Wed, 28 Nov 2012 14:15:00 GMT

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

swuehl — Wed, 28 Nov 2012 14:29:10 GMT

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

What numbers do you expect?

Re: Plotting Normal Dirstribution/ bell curve

Wed, 28 Nov 2012 14:45:20 GMT

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

swuehl — Wed, 28 Nov 2012 14:50:42 GMT

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

Wed, 28 Nov 2012 16:39:12 GMT

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

swuehl — Wed, 28 Nov 2012 16:51:02 GMT

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.

Re: Plotting Normal Dirstribution/ bell curve

manideep78 — Tue, 25 Feb 2014 11:26:00 GMT

Hi John Witherspoon

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