topic Re: Display / determine value of most recent datapoint in chart? in QlikView

Display / determine value of most recent datapoint in chart?

Wed, 20 Nov 2013 11:55:27 GMT

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

Re: Display / determine value of most recent datapoint in chart?

Nicole-Smith — Sat, 23 Nov 2013 15:42:52 GMT

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.

Re: Display / determine value of most recent datapoint in chart?

jagan — Sat, 23 Nov 2013 16:26:40 GMT

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.

Re: Display / determine value of most recent datapoint in chart?

Mon, 25 Nov 2013 14:06:06 GMT

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

Re: Re: Display / determine value of most recent datapoint in chart?

MayilVahanan — Mon, 25 Nov 2013 14:27:07 GMT

PFA

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

Re: Re: Display / determine value of most recent datapoint in chart?

Nicole-Smith — Mon, 25 Nov 2013 14:33:41 GMT

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

Re: Re: Display / determine value of most recent datapoint in chart?

Mon, 25 Nov 2013 15:42:34 GMT

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

Re: Re: Display / determine value of most recent datapoint in chart?

Mon, 25 Nov 2013 16:11:48 GMT

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.

Re: Display / determine value of most recent datapoint in chart?

Nicole-Smith — Mon, 25 Nov 2013 16:26:10 GMT

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.

Re: Re: Re: Display / determine value of most recent datapoint in chart?

Nicole-Smith — Mon, 25 Nov 2013 16:31:10 GMT

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)

Re: Display / determine value of most recent datapoint in chart?

Clever_Anjos — Mon, 25 Nov 2013 16:38:55 GMT

Try

firstsortedvalue(aggr(sum(values),-MonthYear))

Re: Display / determine value of most recent datapoint in chart?

Mon, 25 Nov 2013 17:44:14 GMT

Awesome, Nicole, that approach works a treat.

It also works with the island table so is a more universal solution. I'm guessing without me doing any RTM yet that the '?*' is a wildcard set so it displays any value at the x-axis point where it needs to find one. Whatever, it works 🙂

I tried the Monthly aggregation as

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

but that doesnt work it seems.

-----------

Clever, your method looks promising too. However, if I substitute my fieldnames into the formula you suggested I get an expression error, so unless I did something wrong it looks to be missing a field from the expression syntax. I tried some combinations and on an island table (i.e. without including the common calendar script) I got this expression to work correctly. It gives the value of A on 22/11 in my test qvw.

=Firstsortedvalue(ValueA,-Aggr(sum(WeekDate),WeekDate))

However, if I try the same idea on the monthly aggregation like this it doesnt work - I just get Null.

=Firstsortedvalue(ValueA,-Aggr(sum(Month(WeekDate)),Month(WeekDate)))

I also tried

=Firstsortedvalue(Sum(ValueA),-Aggr(sum(Month(WeekDate)),Month(WeekDate)))

The expression also doesnt work (whether aggregated into months or not i.e. as weeks) when I add in a common Calendar containing a later date than which exists in my plotted data table.

----

Thus, just one last thing to figure out then, showing the last plotted value when doing monthly agrregation in the presence of a common Calendar table.

Thanks to all for your valuable input thus far,

Re: Display / determine value of most recent datapoint in chart?

Clever_Anjos — Mon, 25 Nov 2013 17:52:41 GMT

Firstsorted value has an important behavior, if two or more values share the same rank, null is returned.

In this case, you should slightly change your rank expression.

Into your expressions sum(WeekDate) does not make sense to me.

Is it possible to post a sample of your data?

Re: Display / determine value of most recent datapoint in chart?

Nicole-Smith — Mon, 25 Nov 2013 17:56:02 GMT

You're correct about the ?--it is a wildcard so that we only find dates where ValueA (or ValueB) exist (the length is 1 or greater).

As for this:

It doesn't work because you cannot have a calculation on the left side of the equal sign in set analysis. You would need to create a new field in the load script where you calculate Month(WeekDate) and then use that in the set analysis.

Re: Display / determine value of most recent datapoint in chart?

happydays1967 — Wed, 15 Jul 2015 14:12:35 GMT

Kicking this up and dusting it off, because I have been searching the forum and did not come up with a simple and robust answer. However, I found a solution that works at least for me, although it might take some creative thinking to implement in your specific situation.

The expression has some properties you can set with expressions as well:

One of these is 'show value'. This can contain an expression that evaluates to either 0 (false) or 1 (true) based on the criteria you define. As I said, this involves some creative thinking on how to create a statement that will evaluate to true or false with the right conditions, but it should be possible to accomplish.

It can also be used in line charts to show values on datapoints for only some values in the dimension. 'Show value on datapoint' should be unchecked. Also mind the setting 'Max Values Shown' setting on the presentations tab. If this is too low, values will not show either

Just hope this will save someone else the hourslong quest it has cost me to accomplish this.

Regards,