Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Using calculated value within an expression

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!

3 Replies
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?

Capture.PNG

Anonymous
Not applicable
Author

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