Standard Deviation in Chart (Bell Curve)

[nihhalmca]

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.

[vishalarote]

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.......

[Anonymous]

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)

[vishalarote]

Dimesion : YEAR

Expression : Sum(Distinct Standard_Deviation)

For Above Document.

[nihhalmca]

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

[nihhalmca]

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.

[nihhalmca]

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;

[MarcoWedel]

Hi,

are you looking for something like this?:

hope this helps

regards

Marco

[nihhalmca]

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.

[MarcoWedel]

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

[nihhalmca]

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.?