topic Re: Bell Curve Problem in QlikView

Bell Curve Problem

Anil_Babu_Samineni — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I am trying to create one bell curve chart.

Can you please help me this. Almost done i mess something.

I followed the Henric Cronström. Please help me guys.

The graph indicates

X-Axis --> Score

Y-Axis --> is Avg(Score) for bell curve

I want to show only Y-Axis with Auto generated like 0-100 values.

Please help me..

Re: Bell Curve Problem

sunny_talwar — Wed, 06 Jul 2016 16:56:52 GMT

Are you sure your data follow normal distribution? Henric's example uses NormInv() function to create a normal curve.

Re: Bell Curve Problem

Anil_Babu_Samineni — Wed, 06 Jul 2016 16:58:32 GMT

Sunny,

I don;t know about that. That's why i posted here. I am new to Bell curve.

Can you please help me on this.

Re: Bell Curve Problem

vikramv — Wed, 06 Jul 2016 17:41:22 GMT

Not sure , Whether you are looking for something like the attached one ??

Re: Bell Curve Problem

Anil_Babu_Samineni — Wed, 06 Jul 2016 17:46:27 GMT

Similar to that,

Some changes required

1) X-Axis should be score

2) Y-Axis is normal distribution from 0 to 100

3) Then expression is similar to Avg(Score, [Normal distribution]) like Bell curve which i show the image beside of my image.

- Anil

Re: Bell Curve Problem

vikramv — Wed, 06 Jul 2016 18:06:29 GMT

You have the data which have scores more than 1000 ....

So If we limit the data in you chart to <= 100 , we can see the possible bell curve as far as I know is attached.

Re: Bell Curve Problem

Anil_Babu_Samineni — Thu, 07 Jul 2016 06:23:02 GMT

Vikram,

Yes - Here the curve showing horizontal. How to change this line chart into Vertical. But, i don;t want to effect dim and Expre.

Nice Article.

Re: Bell Curve Problem

hic — Thu, 07 Jul 2016 13:52:01 GMT

If you only select the values around the average, it looks like a normal distribution. But you have outliers, so without this selection it doesn't look as good.

HIC

Re: Bell Curve Problem

Anil_Babu_Samineni — Thu, 07 Jul 2016 14:17:28 GMT

Henric,

Thanks for your response. Here, i want to exclude two values which is from different field. And meantime, Client want to show Y-Axis from 0 to 100 only.

And in my score which mean X-Axis, i have up to 100 only. So, how to achieve this.

I am expecting O/P like this. This graph i created in excel. Is that is possible?

Re: Bell Curve Problem

hic — Thu, 07 Jul 2016 14:35:40 GMT

If you want something like my diagram, but limited to scores under 100, you should use something like

Round([Score],BinWidth)

as dimension, and

Count ({$<[Score]={"<110"}>} [Score])

as measure for the bars. Then you can add a Normal distribution using e.g.

Only({$<[Score]={"<110"}>} Normdist(Round([Score],BinWidth),Avg({$<[Score]={"<110"}>} total [Score]),Stdev({$<[Score]={"<110"}>} total [Score]), 0)) * BinWidth * Count({$<[Score]={"<110"}>}total [Score])

Binwidth could be 10 or 20 or something like that.

I don't understand your graph. What do the y-values correspond to?

HIC

Re: Bell Curve Problem

Anil_Babu_Samineni — Thu, 07 Jul 2016 14:50:27 GMT

Can you please have a look of my excel

In that, 2014 Data sheet i have original data. After that i created one pivot table for without Exclude values.

In this 2014 Data - Please see the name column, In that - I excluded two values

1) Key Message Delivery

2) Sales Objective Category

After that i copied that data, And then i created Main and Standard Demonstration.

Then i create the Normal Distribution which is our Line expression in Qlik.

Make sure, This whole i did in Excel only.

But, qlik i don;t know how to do that. Here, I want to show

1) X-Axis is score which come under the values of 100.

2) Y-Axis might be random with the suitable of Random values ( I mean Normal Distribution)

3) Measure is --> Average (Score, Normal Distribution)

Here, user want to use only Score, rep & name fields only.

I hope you can understand my issue, I apologize my English.

- Anil

Re: Bell Curve Problem

hic — Thu, 07 Jul 2016 15:37:12 GMT

Aah, I see.

I don't think you want a histogram at all... I suspect you want this:

What I have done is the following:

For each Rep, I calculate the average score over the year. (A rep has several scores.) This is your x-value in the above graph.
Then I calculate the position this average score has in a normal distribution using NormDist(). This is your y-value in the above graph.
Then I plot the two using a scatter chart. One Rep = one data point.

HIC

Re: Bell Curve Problem

Anil_Babu_Samineni — Thu, 07 Jul 2016 16:36:20 GMT

Henric,

Almost i reach the target.

Sorry for troubling, That is my miss understanding. Any way, I excludes two values. But, My max score is 100 only not that i have score with 80 only. After excluding those two fields might be X-axis should be Up to 100 / 80 only.

Avg({$<[2014 name] -= {'Key Message Delivery','Sales Objective Category'}>} AvgRepYearScore)

I use this scenario.

Updated : Did you get a chance to look this. I mean how to exclude those two values from this graph

Help me please