Hello all…I am attempting to create some calculated versions that will give me the average, min, and max for some accounts of the current member. Does anyone have some examples that they can point me too so I can revise for my particular situation?
Thanks in advance!
Monte
Hi @monte.southwell, I use calculated versions for many things. What would you be averaging? I’m wondering if a calculated account would be more appropriate.
I would like to get the average, MIN, and Max for 2 accounts:
% to Package
% to BOM
Which are obviously calculated accounts that I have in place.
I guess my thinking was if I could do this with a Version then it could be used for any account…like I said my thinking…lol…
I originally was thinking it would have to be an account for I would need to create 3 for the % to Package…Avg MIn Max…and then 3 for the % to BOM.
If you are able to get give me an example of the 3 using accounts I would appreciate it!
Thanks
Monte
Your original inclination is where I was headed as well.
If you’re looking to average a set of accounts, it will need to be another account. If you’re looking to average over time, then you can set that up as a time perspective, or a set of accounts, or possibly a version. If you’re looking to average the children of a parent node, that can be done in a version as well, but also in the account structure.
For example, for averaging over time, such as “the average over a quarter” or “average over the year”, you can use the Time Conversion property in your Account hierarchy to be “Average”. I use this for headcount and such, where 12 employees each month would add up to 144 for the year, but really it’s only 12. By using an average, I can show the average including any ups and downs. New new accounts required.
But sometimes you don’t want to do in your main hierarchy. So, in that case, I’d create an “average” hierarchy structure outside of my main structure (perhaps as part of my STAT structure) where leaf accounts are exact mirrors of the main accounts using a formula, then changing the time conversion attributes in these calculated accounts.
If you’re looking for an average within the same time period, then you will need to be averaging multiple accounts. You can do this either by creating another account that averages the specific accounts together, or you can combine them into a parent account which aggregates by average rather than the standard SUM operation.
I hear what you’re saying about doing it in a version so that it applies to all accounts, but I still can’t figure out what you’d be averaging in that case. You have a single intersection of account, time, and all your other dimensions. The average, min, or max of that intersection will be that very value.
So, to your specific example of the 2 accounts you mentioned, you can create the following 3 new accounts as formulas. This will work in all versions, based on the calculated versions in those accounts.
In this case I’ve merely added the two accounts and divided by 2 for the average, and used the MIN or MAX function for the other two.
* ([% to BOM]+[% to Package])/2
* MAX([% to BOM],[% to Package])
* MIN([% to BOM],[% to Package])
If you need to get more dynamic, we could use MDX to work on leaf descendants of the parent node (“Formulas” in this example).
Bob this was very very helpful. As always you provided detailed info that I will be able to use to move forward.
You are a Red Carpet MVP!
