CODEWITHSUNDEEP
spotfire
Vinod Neelagiri
Vinod Neelagiri

Reputation: 33

Grand Total Issue in Cross Table of Spotfire

When I use FIRST(CG1) in 'Cell Values' the grand total is not summing up instead its showing one of the values from the result of FIRST(CG1).

Please advise if we have to always use sum(XXX) to get the grand total summed up.

Upvotes: 3

Views: 2005

Answers (3)

Fredrik Lundqvist
Fredrik Lundqvist

Reputation: 26

The ability to use sum in the totals was added at some point. On the properties Totals tab you can select 'As the sum of cell values'. See attached image.

enter image description here

Upvotes: 0

Chelsea
Chelsea

Reputation: 511

Long Answer:

You actually CAN sum the first value of a column from a column grouping.

For example, consider the following data set.

[Grouping] [Food] [Color] [Weight]
Fruit Apple Yellow 4
Fruit Apple Green 2
Fruit Apple Red 4
Fruit Banana Yellow 5
Fruit Banana Brown 2
Fruit Orange Orange 3
Vegetable Carrot Orange 4

If, in your custom expression, you put 

Sum(if(RankReal([Grouping], "ties.method=first", [Food]) = 1, [Weight], 0))

it will find the first instance of each food in your data set, so regardless of how you group the left, your results, subtotals, and grand totals will sum only the first instance of each food.

So you will be able to see the following:

Fruit     Apple        4
            Banana     5
            Orange     3
            Subtotal:  12
Vegetable Carrot  4
            Subtotal:   4
Grand Total:        16

Upvotes: 0

S3S
S3S

Reputation: 25142

Short answer, yes if you want the Grand Total to be a summation of your data. Applying a grand total to a different aggregation will have different results.

  • AVG will average the averages of your Category Axis
  • MAX will take the MAX of the Max for each category
  • Cumulative Sum will take the "Last" value in your Category since it doesn't have any additional values to SUM up.
  • Product will take the Product of Products
  • First and Last you are already aware of.

Upvotes: 2

Related Questions