http://spotfirecommunity.tibco.com/community/search/SearchResults.aspx?o=DateDescending&tag=filters,charts,Spotfire,Custom+expresions,Axis,show,table+column,data+tables,Custom+Node,selected,join+expression,TablePlot,visualization,visualization+layout&orTags=0Search results matching tags 'filters', 'charts', 'Spotfire', 'Custom expresions', 'Axis', 'show', 'table column', 'data tables', 'Custom Node', 'selected', 'join expression', 'TablePlot', 'visualization', and 'visualization layout'en-USCommunityServer 2007.1 (Build: 20917.1142)http://spotfirecommunity.tibco.com/community/forums/p/3327/10073.aspx#10073Tue, 03 Apr 2012 22:02:26 GMTaffde357-0bb3-4331-9204-9726b0672618:10073blevine<p>Any help would be greatly appreciated...</p><p>I have a dataset of people. Each person has different activities they may engage in and amount of calories they may burn. In a HIGHLY simplified state, it essentially looks something like this:</p><table style="width:266pt;border-collapse:collapse;" cellspacing="0" cellpadding="0"> <tr style="height:15.75pt;"> <td style="border-width:1pt 0.5pt 0.5pt 1pt;border-style:solid;border-color:windowtext;width:73pt;height:15.75pt;background-color:yellow;" class="xl80"><strong><font face="Calibri">Participant</font></strong></td> <td style="border-width:1pt 0.5pt 0.5pt 0px;border-style:solid solid solid none;border-color:windowtext windowtext windowtext black;width:36pt;background-color:yellow;" class="xl81"><strong><font face="Calibri">Time</font></strong></td> <td style="border-width:1pt 0.5pt 0.5pt 0px;border-style:solid solid solid none;border-color:windowtext windowtext windowtext black;width:103pt;background-color:yellow;" class="xl81"><strong><font face="Calibri">Calories Burned</font></strong></td> <td style="border-width:1pt 1pt 0.5pt 0px;border-style:solid solid solid none;border-color:windowtext windowtext windowtext black;width:54pt;background-color:yellow;" class="xl82"><strong><font face="Calibri">Activity</font></strong></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl65"><font face="Calibri">John</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl66"><font face="Calibri">12pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl66" align="right"><font face="Calibri">141</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl67"><font face="Calibri">Running</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl65"><font face="Calibri">John</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl66"><font face="Calibri">1pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl66" align="right"><font face="Calibri">210</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl67"><font face="Calibri">Running</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl65"><font face="Calibri">John</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl66"><font face="Calibri">2pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl66" align="right"><font face="Calibri">331</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl67"><font face="Calibri">Running</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl65"><font face="Calibri">John</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl66"><font face="Calibri">3pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl66" align="right"><font face="Calibri">223</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl67"><font face="Calibri">Swimming</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl65"><font face="Calibri">John</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl66"><font face="Calibri">4pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl66" align="right"><font face="Calibri">296</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl67"><font face="Calibri">Swimming</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl65"><font face="Calibri">John</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl66"><font face="Calibri">5pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl66" align="right"><font face="Calibri">95</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl67"><font face="Calibri">Swimming</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl65"><font face="Calibri">John</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl66"><font face="Calibri">6pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl66" align="right"><font face="Calibri">365</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl67"><font face="Calibri">Swimming</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl68"><font face="Calibri">Susan</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl69"><font face="Calibri">12pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl69" align="right"><font face="Calibri">232</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl70"><font face="Calibri">Swimming</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl68"><font face="Calibri">Susan</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl69"><font face="Calibri">3pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl69" align="right"><font face="Calibri">220</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl70"><font face="Calibri">Running</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl68"><font face="Calibri">Susan</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl69"><font face="Calibri">4pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl69" align="right"><font face="Calibri">185</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl70"><font face="Calibri">Running</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl71"><font face="Calibri">Lee</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72"><font face="Calibri">12pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72" align="right"><font face="Calibri">301</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl73"><font face="Calibri">Running</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl71"><font face="Calibri">Lee</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72"><font face="Calibri">1pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72" align="right"><font face="Calibri">314</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl73"><font face="Calibri">Swimming</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl71"><font face="Calibri">Lee</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72"><font face="Calibri">2pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72" align="right"><font face="Calibri">22</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl73"><font face="Calibri">Running</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl71"><font face="Calibri">Lee</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72"><font face="Calibri">3pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72" align="right"><font face="Calibri">384</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl73"><font face="Calibri">Swimming</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl71"><font face="Calibri">Lee</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72"><font face="Calibri">4pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72" align="right"><font face="Calibri">307</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl73"><font face="Calibri">Running</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl71"><font face="Calibri">Lee</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72"><font face="Calibri">5pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72" align="right"><font face="Calibri">468</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl73"><font face="Calibri">Running</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl71"><font face="Calibri">Lee</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72"><font face="Calibri">6pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72" align="right"><font face="Calibri">457</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl73"><font face="Calibri">Running</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl71"><font face="Calibri">Lee</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72"><font face="Calibri">7pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl72" align="right"><font face="Calibri">200</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl73"><font face="Calibri">Swimming</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl74"><font face="Calibri">Avril</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl75"><font face="Calibri">1pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl75" align="right"><font face="Calibri">228</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl76"><font face="Calibri">Swimming</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl74"><font face="Calibri">Avril</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl75"><font face="Calibri">2pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl75" align="right"><font face="Calibri">469</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl76"><font face="Calibri">Running</font></td> </tr> <tr style="height:15pt;"> <td style="border-width:0px 0.5pt 0.5pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15pt;" class="xl74"><font face="Calibri">Avril</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl75"><font face="Calibri">3pm</font></td> <td style="border-width:0px 0.5pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl75" align="right"><font face="Calibri">3</font></td> <td style="border-width:0px 1pt 0.5pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl76"><font face="Calibri">Running</font></td> </tr> <tr style="height:15.75pt;"> <td style="border-width:0px 0.5pt 1pt 1pt;border-style:none solid solid;border-color:black windowtext windowtext;height:15.75pt;" class="xl77"><font face="Calibri">Avril</font></td> <td style="border-width:0px 0.5pt 1pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl78"><font face="Calibri">4pm</font></td> <td style="border-width:0px 0.5pt 1pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl78" align="right"><font face="Calibri">455</font></td> <td style="border-width:0px 1pt 1pt 0px;border-style:none solid solid none;border-color:black windowtext windowtext black;" class="xl79"><font face="Calibri">Swimming</font></td> </tr> </table><p>If I graphed this data in Spotfire with &quot;Activity&quot; on the horizontal axis and calories on the vertical axis (aggregating by min), I would see a value of 3 for running and 95 for swimming.</p><p>&nbsp;But, what I really want is the visual to be of the average minimum (the minimum per participant averaged) of people in this study.</p><p>The min for each participant averaged is 88 for running and 189 for swimming (see below):</p><table style="width:144pt;border-collapse:collapse;" cellspacing="0" cellpadding="0"> <tr style="height:15.75pt;"> <td style="border:0px black;width:48pt;height:15.75pt;background-color:transparent;"></td> <td style="border:0px black;width:96pt;background-color:transparent;" class="xl66" colspan="2"><strong><font face="Calibri">Min</font></strong></td> </tr> <tr style="height:15pt;"> <td style="border:0px black;height:15pt;background-color:transparent;" class="xl65"></td> <td style="border:0px black;background-color:transparent;" class="xl65"><strong><font face="Calibri">running</font></strong></td> <td style="border:0px black;background-color:transparent;" class="xl65"><strong><font face="Calibri">swimming</font></strong></td> </tr> <tr style="height:15pt;"> <td style="border:0px black;height:15pt;background-color:transparent;" class="xl65"><strong><font face="Calibri">John</font></strong></td> <td style="border:0px black;background-color:transparent;" align="right"><font face="Calibri">141</font></td> <td style="border:0px black;background-color:transparent;" align="right"><font face="Calibri">95</font></td> </tr> <tr style="height:15pt;"> <td style="border:0px black;height:15pt;background-color:transparent;" class="xl65"><strong><font face="Calibri">Susan</font></strong></td> <td style="border:0px black;background-color:transparent;" align="right"><font face="Calibri">185</font></td> <td style="border:0px black;background-color:transparent;" align="right"><font face="Calibri">232</font></td> </tr> <tr style="height:15pt;"> <td style="border:0px black;height:15pt;background-color:transparent;" class="xl65"><strong><font face="Calibri">Lee</font></strong></td> <td style="border:0px black;background-color:transparent;" align="right"><font face="Calibri">22</font></td> <td style="border:0px black;background-color:transparent;" align="right"><font face="Calibri">200</font></td> </tr> <tr style="height:15pt;"> <td style="border:0px black;height:15pt;background-color:transparent;" class="xl65"><strong><font face="Calibri">Avril</font></strong></td> <td style="border:0px black;background-color:transparent;" align="right"><font face="Calibri">3</font></td> <td style="border:0px black;background-color:transparent;" align="right"><font face="Calibri">228</font></td> </tr> <tr style="height:15pt;"> <td style="border:0px black;height:15pt;background-color:transparent;"></td> <td style="border:0px black;background-color:transparent;"></td> <td style="border:0px black;background-color:transparent;"></td> </tr> <tr style="height:15pt;"> <td style="border:0px black;height:15pt;background-color:transparent;"></td> <td style="border:0px black;background-color:transparent;"></td> <td style="border:0px black;background-color:transparent;"></td> </tr> <tr style="height:15pt;"> <td style="border:0px black;height:15pt;background-color:transparent;"></td> <td style="border:0px black;background-color:transparent;" class="xl65"><strong><font face="Calibri">running</font></strong></td> <td style="border:0px black;background-color:transparent;" class="xl65"><strong><font face="Calibri">swimming</font></strong></td> </tr> <tr style="height:15pt;"> <td style="border:0px black;height:15pt;background-color:transparent;" class="xl65"><strong><font face="Calibri">Average Min</font></strong></td> <td style="border:0px black;background-color:transparent;" align="right"><font face="Calibri">87.75</font></td> <td style="border:0px black;background-color:transparent;" align="right"><font face="Calibri">188.75</font></td> </tr> </table><p>Is there a way to create an expression to plot this in Spotfire? CUrrently, if I choose min as an aggregation, it will just give me the min across all bins. I need to take the min per person and then average it.</p><p>&nbsp;My real dataset has 120 people, maybe 30 activities and 300 to 4000 rows per person.</p><p>Thanks in advance,</p><p>Brian</p>