- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hey Guys,
I am trying to figure out how to do a mean of the last 6 months of balances. I have a table that I keep on appending to every new end of month. E.g. Bal01012016, Bal 02012016, Bal 03012016, etc..
I have an array as follows:
array T1Table{*} T1:;
T1Last=T1Table[hbound(T1Table)];
Which works fine, I am able to get the Hbound and Lbound values, however, I want to be able to figure out how to get the mean from the last 6 months for balances, so basically get the last T1 Value and do the mean calculation against that one and the previous 5 T1 values..
Any idea on how I can achieve this? My array usage is limited so I don't recall.
Thanks in advance for the help!
Jose S
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
If you are sure that the variables are defined in order, here would be an alternate way to get your last one instead of using hbound:
n_elements = dim(T1Table);
Then you can refer to any array element:
T1_Last = T1Table{n_elements};
T1_Back_1mo = T1Table{n_elements-1};
T1_Back_2mo = T1Table{n_elements-2};
But for this to give you the right pieces to work with, you have to be certain that the elements in the array are in order.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
If you have any control of your data structure change it to a long format. Keeping data in a wide format isn't efficient for so many reasons. This becomes trivial if you switch to a long format.
Otherwise you can use some calculations to determine what your field values should be based on your current date and use VVALUE/VVALUEX to retrieve the correct values and average them.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Are your T1 values separate months within one record or are you looking across records?
How do you define "last 6 months" in terms of the data. There is no indication in your description what may be needed.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@ballardw wrote:
Are your T1 values separate months within one record or are you looking across records?
How do you define "last 6 months" in terms of the data. There is no indication in your description what may be needed.
Yes sorry about that, The data is sequentially ordered by date. E.G. T1-01012016, T1-02012016, T1-03012016
I was defining the last 6 months as Hbound - 6 months basically.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
If you are sure that the variables are defined in order, here would be an alternate way to get your last one instead of using hbound:
n_elements = dim(T1Table);
Then you can refer to any array element:
T1_Last = T1Table{n_elements};
T1_Back_1mo = T1Table{n_elements-1};
T1_Back_2mo = T1Table{n_elements-2};
But for this to give you the right pieces to work with, you have to be certain that the elements in the array are in order.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks! This worked!!
I then did a mean(T1Table{n_elements},......) and it did the trick!