[SOLVED] create an Average column from a sum and a count in spotfire

create an Average column from a sum and a count in spotfire

I retrieve data from database and the sum and the count are automatically calculated.

Something like

Select Data, Product, count(*) as Num, sum(cost) as cost
from myTable
group by Data, Product

From these data directly inputed in Spotfire, I would like to display by date the total cost, and also the average cost. I cannot use the AVG aggregation directly, so I was thinking about using a formula like cost*Num/sum(num) but it is not displaying the right number as sum(num) is not per day...

Is there a way to specify in spotfire formula to sum only per day ? Or another way to do that ?

EDIT : Here are some data

2015/7/1    BIKE    128 3,635,770
2015/7/1    AUTO    487 22,932,530
2015/7/2    AUTO    519 25,219,780
2015/7/2    BIKE    123 3,355,270
2015/7/3    BIKE    135 4,191,060
2015/7/3    AUTO    507 24,687,420

For example, if you use avg on Cost in 2015/7/1, you'll have 13,284,150 (=(3,635,770 + 22,932,530) / 2) But the real average is 18,916,294 (avg on 2015/7/1 independant of product, =(3,635,770 * 128 + 22,932,530 * 487) / (128+487))

Solution

The formula you can use depends a bit on which visualization you are using. If, for example, you use a bar chart, you can use the formula Sum([Num] * [Cost]) / Sum([Num]). But you need to add this as custom expression, not insert calculated column. In the bar chart example, you then have date on the category axis.

I tested the Sum([Num] * [Cost]) / Sum([Num]) formula and it works in most visualizations, e.g. cross table, heat map, bar chart, line chart, etc. But not in the table visualization, because this doesn't aggregate to higher levels.