Using calculated value within an expression

[Anonymous]

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!

[sunny_talwar]

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?

[Anonymous]

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

[sunny_talwar]

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)))