crosstable - How to use grand total in custom expression for spotfire? - Stack Overflow

admin管理员组

文章数量:1289891

I would like to calculate the % of each item using the grand total of qty. Below an example. Any help is appreciated. Can't figure this out...

There's 2 tables.

Below table contains the total qty for a product:

Product	Current_Qty
Product A	1,000
Product B	2,000

I would like to calculate the % of each item using the grand total of qty. Below an example. Any help is appreciated. Can't figure this out...

There's 2 tables.

Below table contains the total qty for a product:

Product	Current_Qty
Product A	1,000
Product B	2,000

Below table contains qty for items in the product

Product	Product_Item	Qty_sold
Product A	Item A1	50
Product B	Item B1	100

Below is the cross table that I have created but not what I wanted The custom expression for % = sum(Qty_sold for Item)/ (sum(qty_sold for Item) + sum(Current Qty))

Item A %	Item B %
50/(50+3,000) = 1.64	100/(100+3,000) = 3.26

I would like to calculate the % of item sold over the grand total of the qty as below:

Item A %	Item B %
50/(150+3,000) = 1.59	100/(150+3,000) = 3.17

Please help thanks.

• spotfire
• crosstable

Share Improve this question Follow asked Feb 21 at 7:56 LambirdLambird 322 bronze badges 1

• your expression does not reference the column names exactly. If you did not join the two tables, this is what worked for me: sum([Qty_sold]) / (sum([Qty_sold]) + sum([table1].[Current_Qty])). – Gaia Paolini Commented Feb 24 at 12:57

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Since you are splitting your cross table by item, I cannot think of a way to sum up over items. But you could create two calculated columns:

table 1: [Total_Qty] as Sum([Current_Qty])
table 2: [Total_sold] as Sum([Qty_sold])

then the expression for your cross table would be:

Avg([Qty_sold]) / (Avg([Total_sold]) + Avg([table1].[Total_Qty]))

I had to add Avg(..) because Spotfire wants aggregations.

本文标签： crosstableHow to use grand total in custom expression for spotfireStack Overflow