Incorrect Subtotal Values in CrossTable

[W Boyers]

Hi There,

I haven't used cross-tables much in spotfire but I am trying to recreate a budget table similar to one made in excel. The original table inputs are pretty much identical to what is shown in the snapshot only unpivoted first and without erros. The snapshot is the spotfire cross table.

What I notice is that the subtotals are incorrect for each Group (A, B, C, etc) - highlighted yellow.

I think this is because,

 

The table requires an aggregation method(vs None to just show the value) so I have to choose Avg([Value]) -blue arrow in image is correct value, but is just the average of a single value- in order to show the correct individual cell values in each category by date. Then the subtotal calculations is just taking the Average of all the values.

What is strange is there is only one entry in each cell in the input data and if I choose Sum([Value]) the cross-table doesnt show correct values anywhere.

 

I have tried other custom aggregations like Sum/Over/Intersect, but can't quite find one that will allow me to keep the values in the individual cells correct AND also subtotal correctly.

Any ideas how I can either create a custom aggregation that simply shows the value (not avg or sum) and/or allows the correct subtotals

Thank you!

[W Boyers]

I figured out that this will work to solve the subtotals issue, but not the grand total which I didn't mention earlier, but is also incorrect.

Expression for Cell Values:

Sum([Value]) / Count([Value]) OVER (Intersect([Date],[Category]))

[Gaia Paolini]

could you share a small data sample (as a file) and what the grandtotal is and what you would like it to be