Calculating Monthly average in a cross table over time

[Abhi Agarwal]

I have a dataset where I need to display the sum of cost over time and the monthly average of value as shown in a cross table below. The time axis can be expanded to display the monthly numbers.

 

 

 

Project Code

2019

2019

2020

2020

 

 

 

 

 

 

Avg Monthly Value

Sum Cost

Avg Value

Sum Cost

Grand Total of Avg Monthly Value

Grand Total of Sum Cost

Expected Grand Total of Avg Monthly Value

 

 

A0

0.04

$4

 

 

0.04

$4

0.04

 

 

A1

2.43

$3,853

3.18

$3,523

 

4.55

 

$7,376

 

2.805

 

 

 

B1

0.29

$93

 

 

0.29

$93

0.29

 

 

 

The expression I used to calculate the Avg Monthly Value =Sum([Value]) / UniqueCount(Month([TIME])) which returnsthe right value for cell. The problem I have is that the row Grand Total of Avg Monthly Value (See row A1). This is returning a weird number when the data over multiple years is available. I expect to see a monthly average over the years.

How do I modify my calculation to achieve that Many thanks for your help in advance.

Here is the raw data:

 

 

 

Project Code

Activity Code

Time

Value

Cost

 

 

A0

A01

11/1/19

0.03

$1

 

 

A0

A01

12/1/19

0.04

$2

 

 

A1

A12

1/1/19

0.8

$67

 

 

A1

A12

2/1/19

0.69

$59

 

 

A1

A12

3/1/19

0.64

$56

 

 

A1

A12

4/1/19

0.53

$67

 

 

A1

A12

5/1/19

0.66

$51

 

 

A1

A12

6/1/19

1.47

$106

 

 

A1

A12

7/1/19

1.12

$99

 

 

A1

A12

8/1/19

0.99

$73

 

 

A1

A12

9/1/19

0.86

$60

 

 

A1

A12

10/1/19

0.88

$58

 

 

A1

A12

11/1/19

0.6

$43

 

 

A1

A12

12/1/19

0.4

$50

 

 

A1

A12

1/1/20

0.65

$43

 

 

A1

A12

2/1/20

0.78

$48

 

 

A1

A12

3/1/20

1.01

$56

 

 

A1

A12

4/1/20

1.08

$46

 

 

A1

A12

5/1/20

0.7

$28

 

 

A1

A12

6/1/20

0.96

$45

 

 

A1

A12

7/1/20

0.34

$37

 

 

A1

A12

8/1/20

0.28

$1

 

 

A1

A13

1/1/19

1.64

$170

 

 

A1

A13

2/1/19

1.16

$177

 

 

A1

A13

3/1/19

1.72

$115

 

 

A1

A13

4/1/19

2.6

$316

 

 

A1

A13

5/1/19

1.8

$246

 

 

A1

A13

6/1/19

2.47

$323

 

 

A1

A13

7/1/19

2.07

$355

 

 

A1

A13

8/1/19

1.03

$159

 

 

A1

A13

9/1/19

0.79

$300

 

 

A1

A13

10/1/19

0.8

$313

 

 

A1

A13

11/1/19

2.28

$327

 

 

A1

A13

12/1/19

1.17

$265

 

 

A1

A13

1/1/20

2.83

$553

 

 

A1

A13

2/1/20

2.31

$378

 

 

A1

A13

3/1/20

2.63

$335

 

 

A1

A13

4/1/20

2.57

$305

 

 

A1

A13

5/1/20

2.58

$424

 

 

A1

A13

6/1/20

2.44

$306

 

 

A1

A13

7/1/20

2.14

$589

 

 

A1

A13

8/1/20

2.14

$328

 

 

B1

B11

1/1/19

0.04

$3

 

 

B1

B11

2/1/19

0.1

$9

 

 

B1

B11

3/1/19

0.16

$14

 

 

B1

B12

1/1/19

0.35

$29

 

 

B1

B12

2/1/19

0.17

$14

 

 

B1

B12

3/1/19

0.22

$14

 

 

B1

B12

4/1/19

0.13

$10

[Fabian Duerr]

Sorry, I think your numbers are not correct. For example the Avg Value for 2019 for A1 can't be 2.4... How did you get this value Maybe you can attache your file here.

Please see attached dxp file with some cross tables and check your numbers again.

EDIT: new file attached

[Abhi Agarwal]

Hi. Thank you for your comment. However, you did not follow what I am trying to ask. In your file, you have calculated avg using the spotfire avg function which is = sum([value])/count([project code]). This is wrong because there are different activities in the same month under the same project code and i want to compute a monthly average when the binned[time] is month and yearly average when the time is set to year. therefore, I need to compute the avg as follows: Sum([Value]) / UniqueCount(Month([TIME])). This gives the right value as I show above. However, the tables inbuilt total function is not right for multiple years. Is there a way to get this to work properly

 

Thanks for your help.

[Fabian Duerr]

Okay, I understand what you want to do. But your 'Expected Grand Total of Avg Monthly Value'' is still not really coorect. The value that you calculated is adding the value from 2019 and 2020 and divide by 2. This is not accurate because you have 12 month in 2019 but only 8 month in 2020. So in your calculation months from 2020 will have a different weigth in the calculation.

 

Anyways, I have created a new column which is Mon-Year (e.g. Jan-2020). This column is basically doing the binning that you need. And you can use this column for your calculation in the cross table. The result looks like this. The new file is attached in my previous answer:

 

[Fabian Duerr]

PS: The reason why you need this new column for your represantation is because you want UniqueCount(Month([TIME])) to evaluate to 20 = 12 + 8. But it will evaluate to 12. It's not any kind of wrong or unexpected bevavior.

 

Of course UniqueCount(Month([TIME])) will evaluate to 12 and 8 for 2019 and 2020 when you do the calculation by year. So this data looks fine.

 

You should always break down your calculation to make sure that you understand what is evaluated.

[Abhi Agarwal]

Many thanks for your help. This works as expected now.