Standard Deviation: Using no timescale and without current period
I require your help in-order to calculate Standard Deviation. The detailed overview of what I am trying to achieve is described below:
I need to calculate Standard Deviation for Daily Sales at Parent Code level for a Depot. My problem stems from that fact that there are no timescales which I can use for line-items and neither is there a current period defined in the model; which renders some scenarios using pre-built functions tad out of scope for me. Hence, I went old school like in good ole' days and started calculating using this method:
1) Found out the SUM of all quantities. (Daily Sales)
2) Calculated the COUNT using Sum function. ( i.e. N no. of data sets)
3) Got the Mean. ( Daily Sales / Count)
Now problem begins at "x - Mean" for me. What is happening is that unlike in excel where I can use a $ sign and fix an absolutely specific value that is subtracted from multiple cells; Anaplan is not doing that for me (I guess probably because I am not able to instruct it correctly). It is directly subtracting that value once and for all from the total.
For instance, if my Daily sales total for a quarter is 1000 for a particular Parent Code, what Anaplan is doing is that it is subtracting my mean lets say 15 from 1000 directly. It is not doing it at individual level for each line item.
I have tried multiple Summary settings including sum and formula but nothing is giving me the desired results. Now, I don't want to attach too many screenshots because that may confuse everyone in understanding my problem but I am attaching a desired excel screenshot and an actual Anaplan module screenshot.
There is one more caveat - I need to perform calculations for quarters though my data to be calculated is at daily level. So my main 'Applies To' are Parent Code, Quarter and Depot while for daily sales calculation at Line item level I keep everything the same except for Quarter, which I switch to Day level dimension (These are separate list mappings - quarters, days etc).
1) The Excel screenshot is as below: The value to extreme right (77354) is nothing but (x - mean) ^ 2 and the value to its left is x - mean and the one to its left is the total sum of all quantities sold.
2) This is what is happening for me in Anaplan:
So what is happening in here is that if i switch the line item summary to Formula it directly subtracts 15.99 (Mean) from total Daily Sales (X) giving me 2783. By keeping Sum as summary I get 1861 as the value for Mean. The thing is that it isn't subtracting just the fixed mean value of 15.99 (In excel I simply used the $ sign to fix it) into all cells individually and giving me the desired mean total.
The subsidiary views are for days level dimension (which I thought should correct it) and the bottom two variance and standard deviation you can see are what I got using quarter level dimensions (They are close to excel values but the decimal seems to be haywire).
I can get the standard deviation easily buy what I need it to do is to get the mean subtracted correctly - I hope I am able to convey myself? (At this point I wish there was some quick chat way possible with community instead of this blogging way but I suppose something is always better than nothing).
Please tell me if there is a way to do that? Since, I am not using any timescales and I don't have any current period defined - correct me If I am wrong - there is no function that can help me here right?
I hope you all are taking care of yourselves and any help will be appreciated!
NB: I tried using the formula directly for variance using : "POWER(Daily Sales - Mean, 2) / (Daily Sales Count - 1)" but to no avail - got the same result.
PS: Sorry if this is some simple mistake and I am taking up your precious time!!!