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,019 Views
Avatar
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
Avatar
Posts: 10
Customer
(@patstevens)
Eminent Member
Joined: 3 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
Avatar
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 the free edition of Analytica

The free version of Analytica lets you create and edit models with up to 101 variables, which is pretty substantial since each variable can be a multidimensional array. It also lets you run larger modes in ‘browse mode.’ Learn more about the free edition.

While Analytica doesn’t run on macOS, it does work with Parallels or VMWare through Windows.


    Analytica Cubes Pattern

    Download the free edition of Analytica

    The free version of Analytica lets you create and edit models with up to 101 variables, which is pretty substantial since each variable can be a multidimensional array. It also lets you run larger modes in ‘browse mode.’ Learn more about the free edition.

    While Analytica doesn’t run on macOS, it does work with Parallels or VMWare through Windows.


      Analytica Cubes Pattern