topic Re: Using calculated value within an expression in QlikView

Using calculated value within an expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Using calculated value within an expression

sunny_talwar — Tue, 05 Jun 2018 12:57:35 GMT

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?

Re: Using calculated value within an expression

Anonymous — Tue, 05 Jun 2018 13:04:22 GMT

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

Re: Using calculated value within an expression

sunny_talwar — Tue, 05 Jun 2018 13:10:51 GMT

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