CODEWITHSUNDEEP
spotfirecrosstable
Lambird
Lambird

Reputation: 3

How to use grand total in custom expression for spotfire?

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.

Upvotes: 0

Views: 20

Answers (1)

Gaia Paolini
Gaia Paolini

Reputation: 1492

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.

Upvotes: 0

Related Questions