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.
Dynamic crosstab with calculations on totals and conditional coloring of values in table - TIBCO Spotfire Community

Dynamic crosstab with calculations on totals and conditional coloring of values in table

Last post Sat, Jan 12 2013 3:39 AM by Conradwg. 0 replies.
Page 1 of 1 (1 items)
Sort Posts: Previous Next
  • Sat, Jan 12 2013 3:39 AM

    • Conradwg
    • Top 500 Contributor
    • Joined on Sat, Jan 12 2013
    • Posts 6

    Dynamic crosstab with calculations on totals and conditional coloring of values in table

    I am hoping someone can provide some assistance on how to handle moving a current analysis performed in Excel into Spotfire. The current Excel analysis pivots data (Product type, Product ID, Problem, Date) into a table. Date becomes the x-axis grouped by month and year. The y-axis utilizes Product Type, Product ID and Problem, with subtotals on Product Type and Product ID for each Problem. This part is easy to replicate in Spotfire with crosstabs.  Problem #1 Once the data is pivoted in Excel, I have calculations performed to create separate control limits for each row. The calculations are average (Cbar), LCL - Lower Control Limit ([Cbar - 3*sqrt(Cbar)] and UCL - Upper Control Limit ([Cbar + 3*sqrt(Cbar)]. However, the calculations do not use the entire data set. The entire analysis contains 24 months of data.  The average and control limits are calculated based on the first 18 months of data only. In Spotfire I know a new table can be created from the old that pivots the data.  I am not sure how to perform multiple "total" calculations on only a subset of the data.  Any recommendations?Problem #2 Once control limits are established for each row then each cell in that row of the pivot table is compared to those limits and conditionally colored (e.g. red if outside of control limits). I am not sure how to handle this in Spotfire. In excel it is done via VBA code.Problem #3 Control chart tests are then performed on each row, including the subtotals, of the Excel Pivot table. This includes whether or not the (1) last x months of values are above or below the average and (2) last x months of values are in increasing order or decreasing order. Indicators are placed in a column showing that a statistical trigger has occurred.  This allows the data to be filtered to show only the rows that have statistical triggers. I am not sure how to do this in Spotfire.

    This analysis has to be done in table format as opposed to actual control charts because there are 1000's of rows in the Pivot table. I am very interested in moving this into Spotfire so that filtering can be utilized in the visualization to dynamically control the content in the table.  So the calculations noted above have to be able to update based on applied filters.

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