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.
S+ - TIBCO Spotfire Community
TIBCO Spotfire Community » TIBCO Spotfire » TIBCO Spotfire S+ » S+

S+

Last post Thu, Mar 29 2012 4:42 AM by tomy11. 7 replies.
Page 1 of 1 (8 items)
Sort Posts: Previous Next

  • Mon, Jan 10 2011 3:11 PM

    • mondash
    • Not Ranked
    • Joined on Mon, Jan 10 2011
    • Posts 3

    S+

    Reply Contact

    My questions is regarding developing a data function that calculates the cumsum.  My sample data, and what I would like would be as follows.  The column I would like to add is in red.  There is going to be more 'sales person's' but this is just an example of the data.

    Sales Person          Date                                      Sales                    *** Sales

    Person1                     1/1/2011                              10                           10

    Person1                     1/2/2011                              15                           25

    Person1                     1/3/2011                              20                           45

    Person2                     1/1/2011                              5                              5

    Person2                     1/2/2011                              10                           15

    Person2                     1/3/2011                              15                           30

    The regular cumsum(x) I don't believe would work.  I am assuming you would have to loop somehow, but don't know where to start.  Any ideas?

  • Wed, Jan 12 2011 4:40 AM In reply to

    Re: S+

    Reply Contact

    Hi Mondash, please add some text to your post. I cannot see any text.

    Christof

    -----------------------------------
    Dr. Christof Gaenzler
    Sr Solutions Consultant
    TIBCO Spotfire

  • Wed, Jan 19 2011 10:11 PM In reply to

    Re: S+

    Reply Contact

    Hi mondash,

    The snippet below suggests two approaches for the S+ script.

    # Create data.

    x.df <- data.frame(person=rep(c("person1", "person2"), each=3),

    date=rep(timeSeq("1/1/2011", "1/3/2011"), times=2),

    gas=c(5 + 5*(1:3), 5*(1:3)), stringsAsFactors=F)

     

    # 1. Here is an approach using cumsum.

     

    # Initialize a column.

    x.df[, "cp1"] <- 0

     

    # Load the built-in bigdata library. It has useful utilities.

    library(bigdata)

     

    # If needed, sort by date within person.

    x.df <- bd.sort(x.df, by.columns=c("person", "date"))

     

    for (peep in unique(x.df[, "person"])) {

    # Compute the cumsum.

    x.df[x.df[, "person"]==peep, "cp1"] <- cumsum(x.df[x.df[, "person"]==peep, "gas"])

    }

     

    # 2. Here is an approach using bd.by.group().

     

    # If needed, sort by date within person.

    x.df <- bd.sort(x.df, by.columns=c("person", "date"))

     

    x.df <- bd.by.group(x.df, by.columns="person", FUN=function(y) {y[, "cp2"] <- cumsum(y[, "gas"]); y}, sort=T)

     

  • Tue, Jan 25 2011 3:34 PM In reply to

    • mondash
    • Not Ranked
    • Joined on Mon, Jan 10 2011
    • Posts 3

    Re: S+

    Reply Contact

    Thanks for the reply, it works great.

     

    In addition to this, what if I wanted to do a difference between two sales persons daily sales, say something like this, where I would select the two people through property control:

     

    Person Date Sales *** Sales % Diff (*** sales)
    Person 1 1/1/2011 10 10 50
    Person 1 1/2/2011 15 25 40
    Person 1 1/3/2011 20 45 33.33333333
    Person 2 1/1/2011 5 5
    Person 2 1/2/2011 10 15
    Person 2 1/3/2011 15 30

    Where I would always have the person with the larger *** sales be the "max" value to calculate the % difference.
    Filed under:

  • Thu, Feb 3 2011 4:01 PM In reply to

    Re: S+

    Reply Contact

    Hi mondash,

    You can use a similar approach based on the result from the previous script.

    # Using x.df generated in the previous response, loop through dates
    # and compute the percent difference between min and max cumulative sales
    # among Persons.

    # First run the original script.

    # Calculate the percent differences in a new data.frame.
    y.df <- 
      bd.by.group(x.df, by.columns=    "date", 
                  FUN=    function(y) {z <- range(y[, "cp1"])
                                       data.frame(y[, c("date", "person")],  
                                              diff.***.perc=100 * diff(z)/max(z))},
                  sort=    T)

     

    # If you want to present only one row per date, use the following.
    yy.df <- 
      bd.by.group(x.df, by.columns=    "date",
                  FUN=    function(y) {z <- range(y[, "cp1"])
                                   data.frame(y[1,     "date", drop=F],
                                              diff.***.perc=100 * diff(z)/max(z))}, 
                      sort=T)

     

  • Fri, Feb 4 2011 10:34 AM In reply to

    • mondash
    • Not Ranked
    • Joined on Mon, Jan 10 2011
    • Posts 3

    Re: S+

    Reply Contact

    Thanks for the response, exactly what I was looking for!  Thanks jminardi.

  • Fri, Jan 20 2012 6:28 PM In reply to

    Re: S+

    Reply Contact

    Depending on the circumstances, using a calculated column instead of S+ and Data Functions could be a simpler solution.  For example:

     Sum([Sales]) OVER (Intersect([Person],AllPrevious([Date])))

    Using the OVER functions can be tricky but with a bit of experimentation they can become quite useful.  Common use cases include calculating moving averages, running totals, and comparisons between parallel periods.

  • Thu, Mar 29 2012 4:42 AM In reply to

    • tomy11
    • Not Ranked
    • Joined on Thu, Mar 29 2012
    • Posts 3

    Re: S+

    Reply Contact

     The snippet below suggests two approaches for the S+ script.

     

     

     

    giant bean bags

     
Page 1 of 1 (8 items)
©Copyright 2000-2011 TIBCO Software Inc | Privacy Policy | Terms of Use I Blog I Contact Us I Content Center