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 Percentage computation of columnar SUB totals or row SUB totals in a cross tab - TIBCO Spotfire Community

Dynamic Percentage computation of columnar SUB totals or row SUB totals in a cross tab

Last post Mon, Oct 31 2011 5:12 PM by jeffrayner. 4 replies.
Page 1 of 1 (5 items)
Sort Posts: Previous Next
  • Thu, May 19 2011 6:14 AM

    • Taher
    • Top 75 Contributor
    • Joined on Mon, Apr 4 2011
    • Posts 28

    Dynamic Percentage computation of columnar SUB totals or row SUB totals in a cross tab

    Hi Christof,
     
    How to display values (values can be summed up) as percentage of columnar SUB totals or row SUB totals in a cross tab ?
     
    So the questions are 1) Is this is supported in a crosstab ? 2) if not what is the other way and the detailed steps/materials to do it ?
     
    If I use the same formula as suggested by you in previous post i.e. Sum([Number]) / Sum([Number]) OVER (all([Axis.Rows]))  here by enabling sub totals in cross tab then spotfire gives the following alert
     
    "Invalid expression for subtotals.
    Subtotals cannot be calculated for an OVER expression that refers to [Axis.Rows]."
     
    So then how to compute and display percentage of columnar SUB totals or row SUB totals in a cross tab.
     
    Any help is appreciated !
     
    Many thanks
    Spotfire
  • Thu, May 19 2011 7:08 AM In reply to

    Re: Dynamic Percentage computation of columnar SUB totals or row SUB totals in a cross tab

    Anyone any solution for this?

    Christof

    -----------------------------------
    Dr. Christof Gaenzler
    Sr Solutions Consultant
    TIBCO Spotfire
  • Thu, Jun 2 2011 11:51 AM In reply to

    Re: Dynamic Percentage computation of columnar SUB totals or row SUB totals in a cross tab

     I need to do the same thing, i.e. calculate the percentage of the total column based on each row in a Cross table (I need to create %total). I have read whatever forum posts exist on the topic but cannot seem to come to a conclusion. 

     

    var         count        %total

     x             100           10%

    x1            200            20%

    x2            700           70%

     

    I have a cross table, and am grouping the rows by two variables on the side. Every time I try to do a sum([Count])  / sum([Count]) over (all([Var])) it does not work and gives me 100% in all columns. I have tried intersect, parent, etc. and nothing seems to work. Any thoughts?

  • Tue, Oct 11 2011 12:06 PM In reply to

    Re: Dynamic Percentage computation of columnar SUB totals or row SUB totals in a cross tab

    First add a total column to the table, using Insert, Calculated Column Sum([count]) As TotCount

     Then in the cross-tab, specify custom expression as Sum([count]) / First([TotCount]) and format the output at percentage

  • Mon, Oct 31 2011 5:12 PM In reply to

    Re: Dynamic Percentage computation of columnar SUB totals or row SUB totals in a cross tab

    it sounds like you have the same issue i had, whereby i wanted to construct a xtab with percents of the subtotal columns or rows

    if so, this two step approach should answer your q

    1) in order to do anything special with charts or tables, one needs to create a calculated field (edit, column properties, insert, calculated column). in my case, i used 1 for the entry in the expression, and called the name counter

     2) then to do a percent of each column, or a percent of each row, use the appropriate equation below as a custom expression (right click on the summing element of the xtab):

    sum([counter]) / sum([counter]) OVER (Parent([Axis.Columns]))

    sum([counter]) / sum([counter]) OVER (Parent([Axis.Rows]))

     

    hope this helps !

     

    PS: note the only difference from yours being the use of Parent, instead of All

    PPS: if you'd like to do a percent of Total, then i used a variant of  vravirala's

    Still using my counter, from step 1, i then created a second calculated column = Sum([counter]) As TotCount

    then created the customer expression as

    Sum([counter]) / First([TotCount]) 

     

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