The Spotfire Community is moving to TIBCOmmunity and this forum location has closed. During the transition, you can still search the old forums but posting has been disabled. We encourage you to pick up the discussion at the new Spotfire community on TIBCOmmunity.
Can someone assist - new to Spotfire and looking to create an "average min" expression - TIBCO Spotfire Community

Can someone assist - new to Spotfire and looking to create an "average min" expression

Last post Wed, Apr 25 2012 11:10 PM by youbing. 4 replies.
Page 1 of 1 (5 items)
Sort Posts: Previous Next
  • Tue, Apr 3 2012 3:02 PM

    • blevine
    • Not Ranked
    • Joined on Fri, Mar 30 2012
    • Posts 3

    Can someone assist - new to Spotfire and looking to create an "average min" expression

    Any help would be greatly appreciated...

    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:

    Participant Time Calories Burned Activity
    John 12pm 141 Running
    John 1pm 210 Running
    John 2pm 331 Running
    John 3pm 223 Swimming
    John 4pm 296 Swimming
    John 5pm 95 Swimming
    John 6pm 365 Swimming
    Susan 12pm 232 Swimming
    Susan 3pm 220 Running
    Susan 4pm 185 Running
    Lee 12pm 301 Running
    Lee 1pm 314 Swimming
    Lee 2pm 22 Running
    Lee 3pm 384 Swimming
    Lee 4pm 307 Running
    Lee 5pm 468 Running
    Lee 6pm 457 Running
    Lee 7pm 200 Swimming
    Avril 1pm 228 Swimming
    Avril 2pm 469 Running
    Avril 3pm 3 Running
    Avril 4pm 455 Swimming

    If I graphed this data in Spotfire with "Activity" 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.

     But, what I really want is the visual to be of the average minimum (the minimum per participant averaged) of people in this study.

    The min for each participant averaged is 88 for running and 189 for swimming (see below):

    Min
    running swimming
    John 141 95
    Susan 185 232
    Lee 22 200
    Avril 3 228
    running swimming
    Average Min 87.75 188.75

    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.

     My real dataset has 120 people, maybe 30 activities and 300 to 4000 rows per person.

    Thanks in advance,

    Brian

  • Wed, Apr 4 2012 7:01 AM In reply to

    Re: Can someone assist - new to Spotfire and looking to create an "average min" expression

    Hello Brian,

    In 2 steps:

    1) create a calculated column:  min([Calories Burned]) over ([Participant],[Activity]) ; this column is called [min per activity and participant]
    2) now you can use any visualisation to show avg([min per activity and participant])

     Kind regards, Andre

     

    Quintus consultants
  • Wed, Apr 4 2012 10:23 AM In reply to

    • blevine
    • Not Ranked
    • Joined on Fri, Mar 30 2012
    • Posts 3

    Re: Can someone assist - new to Spotfire and looking to create an "average min" expression

    Andre, thank you for your fast response.

    Ithink I'm still missing something. If i plug in this calculated column, I get the following result (75.75 min for running and 166.8 min for swimming) - see graph below:

    However, the values I should be getting are 87.75 for running and 188.75 for swimming. When I create a graph with a bar to average the mins, I get the correct values. I just find that the steps and visualizations are not robust for my needs. See the graph below:

     

    I'm not sure if I missed a step in the calculated row suggestion you sent or if I may just be missing something basic. Your email seems to make sense, I just can't seem to get it to work. Is there something I'm missing or skipping?

     Thanks again,

    Brian

  • Thu, Apr 12 2012 10:01 AM In reply to

    • ingo
    • Not Ranked
    • Joined on Thu, Apr 12 2012
    • Germany
    • Posts 1

    Re: Can someone assist - new to Spotfire and looking to create an "average min" expression

    Hello Brian,

    the following steps might be a solution for you:

    Add another two Calculated Columns:

    1)

    Expression: Rank(RowId(), "asc", Concatenate([Participant],[Activity]))

    Name: rank

    This columns identifies the first row of every block, which consists of Participant and Activity.

    In the next step we only use those rows with a value of 1. (Btw. this expression is helpful

    for a lot of other analyses!)

    2)

    Expression: if([rank]=1, [min per activity and participant], null)

    Name: firstmin

    The null value prevents multiple values to be taken into the average calculation, which

    led to the wrong results before.

     

    Now you can put "Avg(firstmin)" on the y axis.


    Hope this helps,

    Ingo

  • Wed, Apr 25 2012 11:10 PM In reply to

    • youbing
    • Top 500 Contributor
    • Joined on Thu, Apr 26 2012
    • Posts 9

    blue beats by dre studio

    In comparison, Blue Beats By Dr Dre Studio the real grounding scheme no need to use the coupling capacitance, has the good low frequency response performance and the headset. In general, the real grounding scheme to provide more low distortion and smaller size scheme. Beats By Dre Headphones is used Beats By Dre Studio Kobe Bryant together with grounding conventional converter, but real grounding structure of the energy efficiency is not blue beats by dre studio high.
Page 1 of 1 (5 items)
┬ęCopyright 2000-2011 TIBCO Software Inc | Privacy Policy | Terms of Use I Blog I Contact Us I Content Center