topic Re: Standard Deviation in Chart (Bell Curve) in QlikView

Standard Deviation in Chart (Bell Curve)

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

Hi All,

I have to calculate average and standard deviation of sales count (year wise). attached sample data here.

Could you please assist on this.

Thanks,

Nihhal.

Re: Standard Deviation in Chart

shiveshsingh — Wed, 18 Apr 2018 18:18:54 GMT

yearwise means you need salesperson average?

Re: Standard Deviation in Chart

Anonymous — Thu, 19 Apr 2018 03:01:54 GMT

try this

Re: Standard Deviation in Chart

nihhalmca — Thu, 19 Apr 2018 04:37:57 GMT

‌yes shivesh, i have to show standard deviation in bell chart.

Re: Standard Deviation in Chart

Anonymous — Thu, 19 Apr 2018 05:02:49 GMT

try change the dimension to saleperson or year if you want year

std dev = aggr( nodistinct Stdev( num([SALES COUNT]) ),SALESPERSON)

avg = aggr(nodistinct avg(num([SALES COUNT])),SALESPERSON)

Re: Standard Deviation in Chart (Bell)

nihhalmca — Thu, 19 Apr 2018 06:58:30 GMT

Hi Rodell, thanks for your response.

And I have checked attachment, it seems unexpected values.

As per your attachment, for year (2014), sales count values as 100, 120, 110 and avg values as follows 110, 110, 110

and stdev values as follows 10, 10, 10. I am suspecting here i think stdev values should follow as -10, 10, 0.

what you say? please advice if am wrong.

Nihhal.

Re: Standard Deviation in Chart (Bell)

Anonymous — Thu, 19 Apr 2018 07:12:25 GMT

hi Nihhal

you are seeing repetitive values because of the NO DISTINCT clause I put in the aggregation function.

for the sake of example and you can remove that

/*avg and std dev per person */

from

std dev = aggr( nodistinct Stdev( num([SALES COUNT]) ),SALESPERSON)

avg = aggr(nodistinct avg(num([SALES COUNT])),SALESPERSON)

std dev = aggr( Stdev( num([SALES COUNT]) ),SALESPERSON)

avg = aggr( avg(num([SALES COUNT])),SALESPERSON)

/*avg and std dev per year */

from

std dev = aggr( nodistinct Stdev( num([SALES COUNT]) ),YEAR)

avg = aggr(nodistinct avg(num([SALES COUNT])),YEAR)

std dev = aggr( Stdev( num([SALES COUNT]) ),YEAR)

avg = aggr( avg(num([SALES COUNT])),YEAR)

Re: Standard Deviation in Chart (Bell)

shiveshsingh — Thu, 19 Apr 2018 07:13:40 GMT

Hi,

Please use dimension as YEAR and expression as aggr(Avg([SALES COUNT]),YEAR) for Average

aggr(Stdev([SALES COUNT]),YEAR) for SD

Re: Standard Deviation in Chart (Bell)

nihhalmca — Thu, 19 Apr 2018 07:31:37 GMT

‌sure i will try this and actually i need to show standard division like bell chart (graphical) not in table charts.

Re: Standard Deviation in Chart (Bell)

shiveshsingh — Thu, 19 Apr 2018 07:36:16 GMT

ok , let me check for bell one.

Re: Standard Deviation in Chart (Bell)

vishalarote — Thu, 19 Apr 2018 07:53:14 GMT

Hello i am Creating Year wise Standard Deviation

and Avg

Hope So it will help you...

Data:

LOAD YEAR,

SALESPERSON,

[SALES COUNT]

FROM

[..\Downloads\Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

left join(Data)

Data2:

load

YEAR,

avg([SALES COUNT]) as Avg

Resident Data

Group by YEAR;

Data3:

Load *,

Sqr([SALES COUNT]-Avg) as Sqr

Resident Data;

Drop table Data;

left join(Data3)

load YEAR,

sum(Sqr) as Sum,

Count([SALES COUNT]) as Count

Resident Data3

group by YEAR;

load *,

Sqrt(Under) as Standard_Deviation;

Data4:

load *,

(1/Count)*Sum as Under

Resident Data3; !

drop table Data3;

drop fields Sqr,Sum,Count,Under;

Find Attachment and Qvw file.......

Re: Standard Deviation in Chart (Bell)

Anonymous — Thu, 19 Apr 2018 08:05:52 GMT

NORMAL DISTRIBUTION Is:

(value - mean)/stddev

in your case

(sales count - avg) / std dev

=(num([SALES COUNT])-

aggr(Avg([SALES COUNT]),YEAR)

)/ aggr(Stdev([SALES COUNT]),YEAR)

Re: Standard Deviation in Chart (Bell)

vishalarote — Thu, 19 Apr 2018 10:02:50 GMT

Dimesion : YEAR

Expression : Sum(Distinct Standard_Deviation)

For Above Document.

Re: Standard Deviation in Chart (Bell Curve)

nihhalmca — Thu, 19 Apr 2018 12:56:21 GMT

Hi Rodell, can you attach sample qvw for normal dist chart (which you posted) else can you share dimension for chart please.

Re: Standard Deviation in Chart (Bell Curve)

nihhalmca — Thu, 19 Apr 2018 13:00:28 GMT

Hi Vishal, thanks for your response.

I can not go this method because need to perform loads multiple times here, i am sure performance issue will occur.

Moreover I believe chart function (stdev) is better than script functions (sqr, sqrt etc) in this scenario.

I need to show data in chart (bell curve) please see attachment.

Re: Standard Deviation in Chart (Bell Curve)

nihhalmca — Fri, 20 Apr 2018 13:48:48 GMT

Hi Vishal - Instead of doing multiple loads, we can go with stdev(SALESCOUNT) right?

Data:

LOAD YEAR,

SALESPERSON,

stdev([SALES COUNT]) as new name

FROM

[..\Downloads\Data.xlsx]

(ooxml, embedded labels, table is Sheet1) group by year, salesperson;

Re: Standard Deviation in Chart (Bell Curve)

MarcoWedel — Sun, 22 Apr 2018 23:27:05 GMT

Hi,

are you looking for something like this?:

hope this helps

regards

Marco

Re: Standard Deviation in Chart (Bell Curve)

nihhalmca — Wed, 25 Apr 2018 10:59:25 GMT

Hi Marco - Thanks for your response.

Some what its helpful. Actually i want to display YEAR at X-axis instead of (valueloop) and i tried it as :

Dim - YEAR

Expre - NORMDIST(YEAR, Avg(SALESCOUNT), Stdev(SALESCOUNT),0)

However chart is not in bell curve. could you please assist on this.

Re: Standard Deviation in Chart (Bell Curve)

MarcoWedel — Wed, 25 Apr 2018 21:03:20 GMT

Hi,

I guess we should first clarify your requirement before discussing possible QlikView solutions.

Can you sketch your expected result based on your sample data?

How should a curve look like that plots the distribution of sales values over time and what information should it deliver?

Do you expect your sales to be normally distributed over time thus having a temporal maximum and to converge towards zero in the years to come?

regards

Marco

Re: Standard Deviation in Chart (Bell Curve)

nihhalmca — Thu, 03 May 2018 12:13:55 GMT

Hi Marco, I am attaching expected visualization.

Mid line is mean and less than mean values should display left side and greater than mean values should display right side. I think this chart is not possible through QlikView. For more details please google it as standard deviation.

Note: Just I provided sample data for this visualization. Can you say this kind of chart is possible in QlikView.?