Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
See the load script in the attached.
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;
Can you try with the follow function:
=Stdev(
Aggr(Sum(Field_1),Dim1,Dim2,Dim3))
/
Avg(Aggr(Sum(Field_1),Dim1,Dim2,Dim3)
)
perfect and thank you