Tibco Spotfire - Calculate average only if there are minimum 3 values in a column - see desc

Question

I want to calculate average in Spotfire only when there are minimum 3 values. if there are no values or just 2 values the average should be blank

Raw data:

Product       Age          Average
1
2
3             10      
4             12   
5             13             11
6
7             18 
8             19 
9             20             19
10            21             20

Answer 1

The only way I could really do this is with 3 calculated columns. Insert these calculated columns in this order:

• If(Min(If([Age] IS NULL,0,[Age])) over (LastPeriods(3,[Product]))<>0,1) as [BitFlag]
• Avg([Age]) over (LastPeriods(3,[Product])) as [TempAvg]
• If([BitFlag]=1,[TempAvg]) as [Average]

This will give you the following results. You can ignore / hide the two columns you don't care about.

RESULTS

+---------+-----+---------+------------------+------------------+
| Product | Age | BitFlag |     TempAvg      |     Average      |
+---------+-----+---------+------------------+------------------+
|       1 |     |         |                  |                  |
|       2 |     |         |                  |                  |
|       3 |  10 |         |               10 |                  |
|       4 |  12 |         |               11 |                  |
|       5 |  13 |       1 | 11.6666666666667 | 11.6666666666667 |
|       6 |     |         |             12.5 |                  |
|       7 |  18 |         |             15.5 |                  |
|       8 |  19 |         |             18.5 |                  |
|       9 |  20 |       1 |               19 |               19 |
|      10 |  21 |       1 |               20 |               20 |
|      11 |     |         |             20.5 |                  |
|      12 |  22 |         |             21.5 |                  |
|      13 |  36 |         |               29 |                  |
|      14 |     |         |               29 |                  |
|      15 |  11 |         |             23.5 |                  |
|      16 |  23 |         |               17 |                  |
|      17 |  14 |       1 |               16 |               16 |
+---------+-----+---------+------------------+------------------+