Skip to content
How to do an "avera...
 
Notifications
Clear all

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

3 Posts
2 Users
3 Reactions
2,720 Views
Posts: 3
Customer
Topic starter
(@6fewdoagajboxbto9hgrxcyfvyo2)
Active Member
Joined: 3 years 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:

 

Many thanks for any help in advance.

2 Replies
Posts: 10
Customer
(@patstevens)
Eminent Member
Joined: 4 years 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: 3
Customer
Topic starter
(@6fewdoagajboxbto9hgrxcyfvyo2)
Active Member
Joined: 3 years ago

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

Reply
Share:

Download Free Analytica


    We hate spam as much as you. We won't share your email with third parties.
    The free edition of Analytica includes these key Analytica features:
    Free Analytica has no time limit. The only constraint is it won’t let you create more than 100 variables or other objects. But your model can be quite substantial since each variable can be a multidimensional array. It also lets you explore, change inputs, and run existing models of any size (excluding features unique to the Enterprise or Optimizer editions).