How to do an "average" by Financial Year if index is by Month
Hi All
I am relatively new to Analytica and hence not very good at applying the correct syntax to do what i want. So please bear with me on this.
I have a table of data consisting of 2 indexes - Month & Places.
Month Index values = 1/01/2019, 1/02/2019, 1/03/2019, 1/04/2019, ... 01/11/2021
Places Index values = Pl_a, Pl_b, Pl_c
Tables values =
Pl_a Pl_b PL_c
1/01/2019 9.39 3.36 9.64
1/02/2019 3.03 3.47 7.76
1/03/2019 0.25 8.29 3.70
1/04/2019 5.15 4.63 6.89
1/05/2019 4.62 2.64 9.91
1/06/2019 9.59 9.59 9.72
1/07/2019 8.19 3.32 5.93
1/08/2019 5.75 3.56 4.24
1/09/2019 1.86 0.97 9.43
1/10/2019 2.39 5.90 9.64
1/11/2019 5.39 4.19 7.94
1/12/2019 1.57 0.18 3.83
1/01/2020 1.42 9.99 8.97
1/02/2020 6.91 4.61 1.66
1/03/2020 2.66 9.61 4.20
1/04/2020 2.57 9.11 2.60
1/05/2020 5.66 2.15 3.49
1/06/2020 4.17 4.14 5.84
1/07/2020 1.75 5.71 1.26
1/08/2020 4.70 4.64 9.20
How can i carry out an average based on Financial Year on these values if the financial year definition is April-Mar of next year. So i am after some code that can achieve results like so:
Many thanks for any help in advance.
That's the Aggregate function. You need a Month_to_fiscal year array (indexed by Month, with the fiscal Year for that month in each cell). Then you use Aggregate like this:
Aggregate( Data, Month_to_fiscal_year, Month, Fiscal_year, "Average" )
Hi Pat. Thank you for your assistance. The function was what i was after. Many thanks.
- 4 Forums
- 84 Topics
- 280 Posts
- 0 Online
- 654 Members