topic Re: % Variance in QlikView

% Variance

Mon, 25 Nov 2013 16:06:01 GMT

Hi There,

Can you please assist me with including a % variance into my output. I would like to display the % variance difference between the servers for the different months. So it always needs to calculate the variance based off the previous months Cost.

Server	Year	Month	Cost	Variance %
Server1	2013	Aug	R 93 783.32
Server1	2013	Sep	R 85 483.91
Server2	2013	Aug	R 69 582.24
Server2	2013	Sep	R 67 723.18
Server3	2013	Aug	R 149 733.25
Server3	2013	Sep	R 159 443.99
Server4	2013	Aug	R 1 272.58
Server4	2013	Sep	R 3 524.06
Server5	2013	Aug	R 152 598.46
Server5	2013	Sep	R 164 023.98
Server6	2013	Aug	R 104 204.40
Server6	2013	Sep	R 125 495.57

Thanks in advance

Re: % Variance

Nicole-Smith — Mon, 25 Nov 2013 16:41:23 GMT

See the load script in the attached.

Re: % Variance

Mon, 25 Nov 2013 20:27:38 GMT

hi Nicole,

Can you please send me the format ... I cant see the attachment

kaashiefl@gmail.com

Re: % Variance

Nicole-Smith — Mon, 25 Nov 2013 20:28:22 GMT

Data:

LOAD Server,

date#(Month & ' ' & 1 & ' ' & Year, 'MMM D YYYY') as Date,

Money#(Cost, 'R # ##0.00') as Cost

INLINE [

Server,Year,Month,Cost

Server1,2013,Aug,R 93 783.32

Server1,2013,Sep,R 85 483.91

Server2,2013,Aug,R 69 582.24

Server2,2013,Sep,R 67 723.18

Server3,2013,Aug,R 149 733.25

Server3,2013,Sep,R 159 443.99

Server4,2013,Aug,R 1 272.58

Server4,2013,Sep,R 3 524.06

Server5,2013,Aug,R 152 598.46

Server5,2013,Sep,R 164 023.98

Server6,2013,Aug,R 104 204.40

Server6,2013,Sep,R 125 495.57

];

DataFinal:

NOCONCATENATE LOAD *, num(if(Server = previous(Server), Cost/previous(Cost)), '#,##0.0%') as [Variance %] RESIDENT Data

ORDER BY Server, Date;

DROP TABLE Data;

Re: % Variance

javier_florian — Mon, 25 Nov 2013 20:33:56 GMT

Can you try with the follow function:

=Stdev(

Aggr(Sum(Field_1),Dim1,Dim2,Dim3))

Avg(Aggr(Sum(Field_1),Dim1,Dim2,Dim3)

)

Re: % Variance

Mon, 25 Nov 2013 20:47:13 GMT

perfect and thank you