Using Text in chart to display last data point on line chart

[#1]

Hi All,

I am trying to display the last data point value from my line chart to a text in chart.  But I do not know how to reference the last data point in the text in chart expressive.  My dimension is [Date] and my expressive is [MV], where [MV] is "=if([Date]=vStartDate,($(vAmt))*(1+[Return]),(above([MV])*(1+[Return])))"

I don't think set analysis work with the above function. I try using the below expressive with no luck:

=(Sum({<Date={"=$(=Max(Date))"}>}[MV]))

[MV] is the label for my vMV expressive column, I don't need to sum.  I just need to somehow reference the value of [MV] where Date=max(date)

Thanks. 

[swuehl]

If you just want to show the last data point value, you can use a conditional formatting of the data point value, maybe like shown here by John:

http://community.qlik.com/message/116857

If you want to format the text in chart in a more complex way or show the value in a different chart object (which should be equivalent to your problem), you could try restructuring your formula:

if RETURN value is constant for all dates, your expression could maybe transformed into something like this:

= FirstSortedValue($(vAmt), Date) * pow((1+[Return]),count(Date))

Depending on your data model and requirements, something like this could work or maybe won't work. It would be helpful if you explain your other fields and the variable $(vAmt) a bit more. At best, you could attach a small sample app to your posting that demonstrate your issue.

Hope this helps,

Stefan

[#3]

Hi Stefan,

Thanks for your help, below are the fields and variables involve:

Fields:

[Date]=date

[Return]=return of fund or index or stock

variables:

vStartDate=variable from input box from user

vAmt=variable from input box from user for initial investment

Hopefully that helps clarified the fields. 

Thanks.

[swuehl]

So vStartDate and vAmt are constant, not depending on your dimension, right?

And Return? Do you select one value, like from a list of percentages 1%,2%,3%...?

If Return is also independent from Date, I think you probably could use something like

=$(vAmt)*Pow((1+[Return]), count(distinct Date) )

which should be no different from a  compound interest calculation. But maybe I've misunderstood your setting completely.

[#5]

Yes, vStartDate and vAmt are constant and independent of the dimension but the returns will vary per the data stream and depends on the Date.