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

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

Fri, 27 Apr 2012 21:25:07 GMT

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.

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

swuehl — Sat, 28 Apr 2012 11:57:02 GMT

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

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

Mon, 30 Apr 2012 17:17:40 GMT

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.

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

swuehl — Mon, 30 Apr 2012 21:04:10 GMT

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.

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

Tue, 01 May 2012 16:14:10 GMT

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.