Standard Deviation: Using no timescale and without current period
Hi Community,
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!!!
Kind Regards,
Jay
Best Answer
-
@CommunityMember119402 (Jay)
Great question.
There are a few ways to solve this but I like to use the D.I.S.C.O. Methodology. In this case we will create the following:
- 1 Transaction module that has your values that you want the standard deviation
- 2 System modules. One for date and one for quarters
- 1 Calculation module. This will transform our data at the quarterly level
I like to reuse modules that use the same dimensions. Some people don't like that because it can violate the PLANS methodology because it makes it harder to trace the calculations. Up to you but definitely keep your calculations simple. So in this example, I reuse the transaction module to calculate the individual variances.
Start by making sure you transaction module is clean.
In this case I created daily sales units for 3 department.
Here is our transaction module in it's final state.
We will need to add some line items after we've calculated the quarterly values.
Transaction module blueprint:
Date System Module
Used for lookups and filtering. Notice we calculate the quarter period once here. No point in doing this in the transaction module since we have extra dimensions.
Date system module blueprint:
Quarterly System Module
Only created this for filtering.
Quarterly Blueprint
Calculation module
- Turn off summary on all line items (not needed)
- Sum up transactions at quarterly level. Do your calculations here. Very efficient!!
Calculation Blueprint
6
Answers
-
Hi @JaredDolich (Jared),
Thank you for your prompt response and this is really helpful. Having said that there is still that constraint for me - I cannot use any timescale. The only way to use a timescale is in form of a list as mentioned earlier (such as QUARTERS and DAYS). I am attaching a screenshot below for your reference -
Below is a screenshot of how my Data Input module looks like which I have created: (I have kept it entirely for input purposes and I am not performing any calculations there)
I honestly, like your solution but the problem is that I cannot use timescales at all! The model is not using any native timescale in modules; instead fake timescales are being used in form of lists which follow a customised weekly format. So as you can see in my "Applies To" I am using these lists that are acting as my time dimensions.
Now, my new query is that can I still achieve the solution using your way minus the timescale or is there some way to achieve that without it? I am quite new to Anaplan, so I am really a curious George or average Jay next door with lot of questions and by default a lot of comparison is happening in my head due to previous tools which I have used.
For instance, like I stated, I can simply achieve this in excel by subtracting the fixed mean value using $ symbol from each individual cell and then use something basic like "=sum(a1:a125)" to get the final total of all the cells after an operation has been performed on them.
Any help will be appreciated and I am really looking forward to hear from you. Wish you a lovely evening!
Kind Regards,
Jay
0 -
0
-
Jay, can you create your own modules? If so, then change the timescale to quarters and days.
Every model has a selected calendar - it's not optional. Since your transaction data has the date in it, you can map the entire file into a dimensional module (one with time).
Without time, you will have to create a separate line item for each quarter, or use the "fake" time scale.
In the case of a fake time scale you will need to create a calculation module that maps all the transaction data into the fake quarters. You may also have to add a system module to your fake calendar to map days to quarters.
1