Formulae: Calculating dynamic averages

rajeshbalu29 · July 26, 2012 2:24AM

Hi All,
I have a function as below in a column of the table view which has 3 different columns as
1:Dept
2:Activity type
3:FTE
4:Level1
5:Level2

I need to take an average of FTE splited by activity type which I have derived by

SUBSET_SUM([Fte],SUBSET([Activity Type],"ADM"))/NUMVALUE(DATETOTEXT(TODAY,"MM)

But this values will be static for activity type for ADM there are multiple activity types for which i have done a nested IF condition but I need to get some thing where the average can be dynamically by filtering other fields in my Bar view.

For example
ADM total average is 29.50
If i filter it for Dept as "X dept" the, average should change to 15.54
If I filter it for Level1 as "Y level" then average should change to 12.24

Please let me know if there is any way I can achieve this view.

Please let me know if you need any further information

Thanks and regards - Rajesh B

paola · July 26, 2012 6:50AM

Rajesh, if you remove "ADM" from the formula, your Subset function will work and have different results for fields with different Activity Type.
Please send us the file (or sample data) if you want someone to look at this.
Thanks

rajeshbalu29 · July 27, 2012 2:41AM

Hi Paola,
I have sent you the file and the details which iam expecting in the bar chart to you seperately in a mail to your id.

Regards
Rajesh B

paola · July 31, 2012 6:11AM

Hi Rajesh,
we responded directly to your email, and I just wanted to highlight that you can leave function Subset[Field] without specifying the values, and it will return different result against different values in the selected field/ or for each combination of selected fields.
Your NUMVALUE formula is a good way to calculate number of months in YTD.

Subset formula with 1 Field:
SUBSET_SUM([Fte],SUBSET([Level1 Code]))/
NUMVALUE(DATETOTEXT(TODAY,"MM"))

Subset formula -combination of 2 fields:
SUBSET_SUM([Fte],SUBSET2([Activity Type],[Dept_L5]))/
NUMVALUE(DATETOTEXT(TODAY,"MM"))

Welcome!

Categories

Ideas Parade

Tagged