
1. Re: Weighted Average of Dimensions?
Shinichiro Murakami Oct 13, 2016 4:01 PM (in response to Rajul Parekh)Talking about age, why don't you use formula like
[Age 2]
2016int([CP  Year founded])
Talking about display, you can not add ref line on discrete axis, so prepare something like below and add to label.
[Fixed Age]
if attr([Age])= "610 Years"
then "Average Age= "+str(attr(round({fixed :avg([Age 2])},0)))
end
Thanks,
Shin

Data_v02_SM_10.0.twbx 1.7 MB


2. Re: Weighted Average of Dimensions?
Rajul Parekh Oct 14, 2016 10:15 AM (in response to Shinichiro Murakami)Hi Shin 
Thanks so much for your help. This doesnt make sense to me though... how can the average be 9? Shouldn't the average age of all the companies be probably between 23?
Also, I'm not clear on the formula you created for Age 2. Why do we reference "610 years"? And is there any way to make the average mark a vertical line?
Thanks,
Rajul

3. Re: Weighted Average of Dimensions?
Shinichiro Murakami Oct 14, 2016 10:24 AM (in response to Rajul Parekh)Reference line requires continuous axis, so you can not draw it with reference line on this format..
You can check raw data, but high average is driven by a few extreme numbers.
The reason I pick "610 years"? is it's around the center, you can remove that part, then you see the label on top of all the bars.
Thanks,
Shin

4. Re: Weighted Average of Dimensions?
Rajul Parekh Oct 14, 2016 11:05 AM (in response to Shinichiro Murakami)Got it! Checked the data and you are correct. That makes sense!
Do you know how we can tweak the formula to remove these large values (outliers) that are skewing the average?

5. Re: Weighted Average of Dimensions?
Shinichiro Murakami Oct 14, 2016 11:13 AM (in response to Rajul Parekh)One example with excluding >20, you can change the criteria as you like.
[Fixed Age]
"Average Age= "+str(attr(round({fixed :avg(if [Age 2]<20 then [Age 2] end)},0)))
Thanks,
Shin

Data_v02_SM_10.0 V2.twbx 1.7 MB


6. Re: Weighted Average of Dimensions?
Rajul Parekh Oct 14, 2016 11:39 AM (in response to Shinichiro Murakami)This is so helpful, Shin! Thank you so much, it works.
Last question related to this topic  I got these formulas to work for Avg Age and Avg # Employees but the Funding one is a little tricky because there is no measure tied to it and the raw data is in buckets already.
How can I apply what we just went through to find an Avg Funding? It won't be a discrete number but a bucket that most companies fall into.

7. Re: Weighted Average of Dimensions?
Shinichiro Murakami Oct 14, 2016 12:06 PM (in response to Rajul Parekh)No idea. it's anyways, not correct if you don't have raw data.
BTW, could you mark my answer as correct.
Thanks,
Shin