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
- 87 Topics
- 283 Posts
- 1 Online
- 1,874 Members