
1. Re: how to find percentage of positive count in a table?
Tracy Rodgers May 14, 2012 10:23 AM (in response to huanhuan wang)1 of 1 people found this helpfulHi Huanhuan,
I believe you're on the right track. You'll just have to do a second calculation. The above calculation would need to be used in something similar to the following:
sum([above calculation])/total(count([products]))
Hope this helps!
Tracy

2. Re: how to find percentage of positive count in a table?
huanhuan wang May 14, 2012 10:35 AM (in response to Tracy Rodgers)Hi Tracy,
Thanks for the reply, i've tried that earlier and it still doesn't work. it does not allow me to use the sum function saying the calculation is already an aggregated value. so i just use '[above calculation]/total(count[products]). The numerator kept returning 1 instead of the actual count for 'above calculation'.
thanks,
Huanhuan

3. Re: how to find percentage of positive count in a table?
Tracy Rodgers May 14, 2012 10:39 AM (in response to huanhuan wang)Huanhuan,
Would you be able to post your packaged workbook (twbx file) or screenshots of the view and calculation?
Tracy

4. Re: how to find percentage of positive count in a table?
huanhuan wang May 14, 2012 11:27 AM (in response to Tracy Rodgers)Hi Tracy,
i think the problem comes from my calcualtion for the 'impact', i used sum functions to calcualte the impact so later when calculate the differences for impacts, it's automatically becomes aggregated values. here is the example. i have to use the sum function to find the impact because the different granularity levels. and another problem is when using sum, it sums everything instead of summing for distinct products (if positive, sum only once).
Million thanks,
Huanhuan

example.twb.zip 3.4 KB

EXAMPLE1.xlsx 21.0 KB


5. Re: how to find percentage of positive count in a table?
Tracy Rodgers May 14, 2012 12:00 PM (in response to huanhuan wang)1 of 1 people found this helpfulHi Huanhuan,
You can change the positivecount calculation to be the following:
window_sum(if sum([meet goal 1])/sum([total])sum([meet goal 2])/sum([total])>0
then 1
else 0
end)
Then, the %positive calculation should work. A couple of things to note:
1. Since this is now a table calculation, once the calculation is on the view, right click on the calc and select Compute Using>Table (Down)
2. The grand total for positivecount will be incorrect.
Hope this helps!
Tracy

6. Re: how to find percentage of positive count in a table?
huanhuan wang May 14, 2012 12:57 PM (in response to Tracy Rodgers)Hi Tracy,
Thanks for the tip with window_sum! it solves the aggregate problem. however the value doesn't seem right. so finally i gave up the aggregation and use some raw value from excel and changed the comuting using 'cell', it returns the correct answer for nonaggregated values.
Billion thanks,
Huanhuan