Display / determine value of most recent datapoint in chart?

[#1]

Hi,

I'm trying to achieve either of the following - refer the yellow highlighted text. Essentially I wish to label the last data point on a chart with its value, to avoid users having to mouseover to see the value which is difficult for mouse challenged users especially with narrow bars or line graphs to get the pointer placement.

Now I know one can check the display datapoints options in the expressions. However, if one has a lot of bars the values overlap are illegible anyway. I really just want to display the latest value.

I tried adapting the code from where a method was demonstrated to plot the legend/key on the line chart itself,

Qlik Tips: Lose the Legend in Line Charts

The syntax suggested there was along the lines of

Dual( If(MonthYear=Max(total MonthYear), CategoryType, ''), Sum(Sales) )

I'm not totally clear on the fields I should be using but I assumed MonthYear above is the Date dimension field which is the X axis of the chart and Sales is the actual value (line) being plotted, so the logic is that if the MonthYear is the latest one then to display the CategoryType name next to it. In my case I'd need to replace CategoryType with the value of the latest datapoint I think.

Thus if I have a chart (as shown) and lets assume I have a X axis dimension called Dayfield and a plotted bar value for each Dayfield called [Marker Efficiency].

I can plot something on the right hand bar, but its always Null, so I think my issue more is with determining the value of the latest bar.

I then tried creating a variable, adding it in my load script after the MarkerEfficiencyTable was loaded

LET vMarkerEfficiency_latest = Peek('[Marker Efficiency]',-1,'MarkerEfficiencyTable');

I was then just going to use this value with ='Latest value =' & $(vMarkerEfficiency_latest)

to get it to display.

Once again I only get Nulls for the variable but the data clearly exists in memory somewhere because the chart plots fine.

How can I do this?

Thanks

[Nicole-Smith]

Try taking a look at the attached.  It should give you some clues as to how yours needs to be set up.  If you need further assistance, you will need to post your .qvw.

[jagan]

Hi,

Try like this

In chart Properties -> Presentation->Text in Chart -> Add Button -> Text (Give this expression)

='Latest Value = ' & Sum({<DateDimensionName={'$(=Max(DateDimensionName))'}>} MeasureName)

Regards,

Jagan.

[#4]

Hi Nicole and Jagan,

You both suggest very similar methods. I was ia trying this type of approach but it didnt work for me ito getting correct output. Even now I get Nulls, so clearly I'm not doing something right. I even tried reproducing most of the example.qvw provided but adding my own test data. The example.qvw output is perfect, however.

I have attached a test QVW.

As you will see I have some inline data - and I'm getting the date input as DD/MM format.

I then map this to an autogenerated Calendar. Whether I use the Calendar or just the inline table data only, I still dont get this to work.

It seems QV recognises the x-axis data as a date and then even though my last plotted value is in November it seems to be trying to use December as the max(Date). Also maybe its because my date field is in weekly periods - the last Friday of every week rather than day date sequential.

[TestData]:

LOAD * INLINE [

WeekDate, ValueA, ValueB

05/04, 14, 243

12/04,    34,    220

19/04,    65,    150

Can you shed any more light? Thanks

[MayilVahanan]

Hi

PFA

Your Max weekDate is not in dateformat and Maximum week date is 22/11..

[Nicole-Smith]

Because you have two expressions, you need to go the text in chart route (see attached):

[#7]

Hi Mayil,

Good point, I hadnt actually looked at Nicole's DateFormat in example.qvw. This was different from mine.

When I SET DateFormat='DD/MM'; in my script.

Nicole's code works fine with my weekly period data, and with Jagan's code if I just add the Date() function to the Max expression it works too, i.e.

='Latest Value = ' & Sum({<DateDimensionName={'$(=Max(Date(DateDimensionName)))'}>} MeasureName)

Thanks

[#8]

Hi Nicole,

Thanks, I just tried that but it appears there are some caveats - if the text + value field length is longer than the space available before the first data value plot, not all the text shows - one just gets an elipsis  e.g. Latest Value A = ... Also, possibly because I'm using the same x-axis field in both expressions, if I add a 2nd line, for Value B that seems to overwrite the first one for Value A (or at least both texts appear on top of each other making it illegible)

Anyway, no crisis because if I use the same methodology and enter both as a single concatenated expression in 'Show title in chart' I get everything to display. This works for me too. i.e. I did this.

='Latest value A =' & Sum({<WeekDate={'$(=Max(Date(WeekDate)))'}>} ValueA) & ' Latest value B =' & Sum({<WeekDate={'$(=Max(Date(WeekDate)))'}>} ValueB)

The above solves my issue assuming I only have 1 data table with a date dimension (or its a data island in a bigger qvw) which sadly is rarely the case since my models get complex fast.

1. I'd still like to be able to get both values A and B plot above the bar sometimes but using your If statement method it only plots on the last bar, whereas to get Value A to plot one would also need to plot on the 2nd last bar. I did try -7 from the period in the expression but that didnt seem to work.

2. More critically, this method breaks when I link the table date field WeekDate to a common calendar which has a last date > island Max(Date(WeekDate)) (of 22 Nov in my case) because then Max(Date(WeekDate)) = the last date in the autogenerated common Calendar (in my case that is 31/12/2014) since this is part of a greater dashboard containing forecast values with additional date fields all called WeekDate. I have the calendar code commented out in the attached qvw. I get that the issue is likely with the use of the =Max function so is there any way of getting this to work with displaying the value of the last date record in a table even if there are even later date fields with the same date fieldname in the qvw used in other tables?

3. Possibly related to 2, but it still doesnt work for me when I do it standalone (without a common calendar) is how do I get this method to work if I aggregate the island date field into a date group - e.g. weeks into months. I tried that by making =Month(WeekDate) as the dimension and as an expression

=If(Month(WeekDate) = $(=max(Month(WeekDate))), sum(ValueA), '')

but this doesnt plot anything.

Thanks for your time thus far,

I attach an updated test qvw.

[Nicole-Smith]

With two expressions, you're not going to be able to display the latest value over each of the two latest bars.

As for the problem with it cutting off the text and overlapping, if you hold down CTRL+SHIFT after clicking on the chart, you can move around stuff inside of it.

I haven't had a chance to look into the problem you're getting when adding the extra calendar table.  I can try to get back to you on that.

[Nicole-Smith]

Okay, so when you add the calendar table in, you need to change your title in chart to:

='Latest value A =' & Sum({<WeekDate={"$(=Max({<ValueA={'?*'}>}Date(WeekDate)))"}>} ValueA) & ' Latest value B =' & Sum({<WeekDate={"$(=Max({<ValueA={'?*'}>}Date(WeekDate)))"}>} ValueB)