Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm looking to create a rolling 3 month MV expression using another column in my table that is itself a calculated expression ("Monthly Avg. MV"). That original expression is calculating the average MV for a particular month based on daily data in the "Main Table". I am currently using the following expression by referring to the label of the original column in my table but it is not working.
RangeAvg(Above([Monthly Avg. MV],0,3)))
For example, as of 3/31/2015 for Account A, the rolling average 3-month market value should be $55,339.29 which is the average of the average MVs for 1/31/15, 2/28/15 and 3/31/15.
Is it possible to embed this expression within another expression? Thanks in advance for all your help!
I made some script changes to make this work... the only thing which doesn't really match is the Monthly Return... but see if the new ones are actually want you want?
Hi Sunny,
Yes. It looks like the average MV is being calculated using the column 5 of the table rather than the original data set which is exactly what I want. Can you share the script with me? I'll take a look at the monthly return column once you share that.
Thanks!
Shaan
I attached the app with my response above...
Re: Using calculated value within an expression
New script
DailyReturnTest:
LOAD Date,
Account,
Return,
MV,
MonthName(Date) as MonthYear,
Date(Floor(MonthEnd(Date))) as MonthEnd,
Day(Date(Floor(MonthEnd(Date)))) as LastDay
FROM DailyReturnTest.xlsx
(ooxml, embedded labels, table is Sheet1);
Chart
Dimension
MonthEnd
Account
Expressions
=Sum({<Date = {"=Floor(Date) = Floor(MonthEnd(Date))"}>}MV)
=Sum({<Date = {"=Floor(Date) = Floor(MonthEnd(Date))"}>}Return)
=If(Sum({<Date = {"=Floor(Date) = Floor(MonthEnd(Date))"}>}MV) <> 0, Avg({<Date>} MV))
=If(Sum({<Date = {"=Floor(Date) = Floor(MonthEnd(Date))"}>}MV) <> 0, Sum(Return))
=If(Sum({<Date = {"=Floor(Date) = Floor(MonthEnd(Date))"}>}MV) <> 0, RangeAvg(Above([Monthly Avg. MV], 0, 3)))