LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: lauraw_21

Hi

Would anyone be able to help me with script for calculating Prior Year or MAT?

Thanks
Laura

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Here it is broken out to be readable:

today=date()
yearago=today.dateAdjust(-1,"years")
lastyear=yearago.year()


but you can also combine it into a single expression this way:
lastyear=date().dateAdjust(-1,"years").year()

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: lauraw_21

Hi,

This helps me work out what a year ago was but I have a table of transactional values by month and I want to calculate a MAT figure of all of those transactions, like wise with Prior MAT?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

I'm not familiar with the term MAT. Would you define it for me?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: lauraw_21

Sorry, it means "Moving Annual Total"
So MAT for October would be (Nov14-Oct15) but when you move to November it would be (Dec14-Nov15)

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Copy the code below and paste it into your graph:

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: awilliams1024

You may also want to investigate the Rolling Statistics experimental node that was released in the Lavastorm Analytic Library (LAL) updates from release 38. The purpose of the Rolling Statistics node is to generate statistics for a rolling 'window' of records. It provides stats for each window including: sum; min; max; mean; median; mode; count (of non-Null records in the window).

Unlike the example provided by Stony, the Rolling Statistics only outputs records once sufficient records have been read to create a complete window period, so there are no output records for the first eleven records in the input data when the window size is set to 12 (months) for MAT.

The experimental nodes are not visible in the library palette by default. To make them visible within the BRE client select �Tools� then �Preferences�. On the General Tab select the �Advanced�� button and check the �Use Experimental Nodes� option. The Experimental nodes should now appear in their relevant node categories.

The following example extends Stony's to also include the Rolling Statistics node [Copy and paste the code into the canvas as stated for Ston'y reply].


node:BRD_Data
bretype:core::Static Data
editor:Label=Month, Qty
editor:sortkey=560d717e037a2272
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
Month:date,Qty:int
2013-01-01,757
2013-02-01,467
2013-03-01,857
2013-04-01,879
2013-05-01,45
2013-06-01,611
2013-07-01,529
2013-08-01,447
2013-09-01,821
2013-10-01,675
2013-11-01,313
2013-12-01,319
2014-01-01,317
2014-02-01,483
2014-03-01,745
2014-04-01,15
2014-05-01,349
2014-06-01,147
2014-07-01,713
2014-08-01,615
2014-09-01,293
2014-10-01,491
2014-11-01,545
2014-12-01,527
2015-01-01,381
2015-02-01,203
2015-03-01,625
2015-04-01,431
2015-05-01,245
2015-06-01,235
2015-07-01,977
2015-08-01,247
2015-09-01,765
EOX
editor:XY=210,100
end:BRD_Data

node:Rolling_Statistics
bretype:lal1::Rolling Statistics
editor:sortkey=56322c3126cc4b35
input:@556ae2fe01d8016a/=BRD_Data.40fe6c55598828e5
editor:bend=0=260|100
editor:bend=0=260|210
output:@556ae2ff26573558/=
prop:DataFields=Qty
prop:WindowSize=12
editor:XY=310,210
node:Build_Buckets
bretype:::Build Buckets
editor:shadow=558af212386329ba
input:@40fd2c74167f1ca2/=
output:@40fd2c7420761db6/=
end:Build_Buckets

node:Rolling_Statistics_Engine
bretype:::Rolling Statistics Engine
editor:shadow=558af212628056b8
input:@556ab45c726008d7/=
input:@556ab45c7ba04087/=
output:@556ab463785f2c2f/=
end:Rolling_Statistics_Engine

node:Single_Bucket
bretype:::Single Bucket
editor:shadow=558af2120fa110a7
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Single_Bucket

node:Bypass
bretype:::Bypass
editor:shadow=558af2120507117d
input:@556ae7e776bd7a95/=
input:@556ae7e952260f55/=
output:@40fd2c7436717256/=
end:Bypass

end:Rolling_Statistics

node:Agg
bretype:core::Agg
editor:sortkey=560d71847f147381
input:@40fd2c7427456e5b/=BRD_Data.40fe6c55598828e5
output:@40fd2c744c862db0/=
prop:GroupBy=<<EOX
1
EOX
prop:Script=<<EOX
if firstExec then qtylist=list(0)
qtylist=flatten(list('Qty'),slice(qtylist,0,11))
MAT=apply(&+,qtylist)
emit *,str(qtylist) as values,MAT

EOX
editor:XY=310,100
end:Agg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: lauraw_21

Thank you, I will give these a go and let you know how I get on