Notifications
Clear all

How to do an "average" by Financial Year if index is by Month


Posts: 2
Customer
Topic starter
Active Member
Joined: 4 weeks ago

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:

image

 

Many thanks for any help in advance.

2 Replies
Posts: 9
Customer
Eminent Member
Joined: 5 months ago

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" )
Reply
Posts: 2
Customer
Topic starter
Active Member
Joined: 4 weeks ago

Hi Pat. Thank you for your assistance. The function was what i was after. Many thanks.

Reply
Share:
Scroll to Top