0

I am new to Spotfire and one of my clients has asked for cross table report. I have created one but the sub-total values are wrong as showin in the image below

For creating report I have merged the all tables into one table.

Results

Department:  
Department  Customer    Emp Hours  
A   A1  AA  7  
A   A1  AA  6  
A   A2  AB  8  
A   A3  AA  8  
B   B1  BA  9  
B   B2  BB  5  
B   B2  BB  7  
B   B3  BA  5  
C   C1  CA  5  
C   C2  CB  8  
C   C1  CA  6  

Budget Hours:  
Customer    Emp Forecasted Hours  
A1  AA  13  
A1  AB  13  
A2  AB  12  
A3  AA  13  
B1  BA  12  
B2  BB  14  
B3  BA  15  
C1  CA  14  
C2  CB  16 



Fee Rate:  
Customer    Emp Rate  
A1  AA  1.5  
A1  AB  1.2  
A2  AB  1  
A3  AA  1.3  
B1  BA  2  
B2  BB  2  
B3  BA  1.5  
C1  CA  1.6  
C2  CB  1

I have asked the same question in Spotfire community but no one has responded.

Could you please help me to resolve the issue?

Thanks in advance for your help.

S3S
  • 24,809
  • 5
  • 26
  • 45
Shaik
  • 11
  • 1
  • 1
    @Shaik- How did you get 'cust_exp' and 'cust_exp_2' columns? I don't see those columns in your original tables. – ksp585 Dec 13 '16 at 13:30
  • thanks for your reply, I have joined the all the tables and made one table.do to joining, target values are duplicating, for removing duplication I am taking ratio value. Cust_exp: Sum([Forecasted Hours]) / Count([Emp]) I am also attached spotfire community link for reference. There you can download sample data and application also. – Shaik Dec 20 '16 at 07:31

2 Answers2

0

The bottom line is that your sub-total is going to be on the underlying data, regardless of what you are displaying using your custom expression. You can't create "custom" sub-totals which are based off only what you are displaying through your expression--it's always calculated off the data which is feeding the analytic.

Check out this post for a similar answer: Spotfire - Custom sub total

Community
  • 1
  • 1
S3S
  • 24,809
  • 5
  • 26
  • 45
0

I don't know what your expected values are but at the bottom of where the totals are calculated there are two options. The default is underlying row values which is what I think you are using and the other is as sum of cell values. This may yield the results you want.
If that isn't the case you will need to "identify" that you are in the subtotal or total line and then modify your calculation if you are in that situation. This isn't a simple solution but it will work. I have done this in the past and it isn't fun but it can be accomplished. I can try to dig up the code for this if this is the case for you.

Luvigz
  • 1