topic Re: Display data point values on line chart in QlikView

Display data point values on line chart

Anonymous — Wed, 18 May 2011 00:06:14 GMT

Hi there,

I have a line chart with an x-axis time series (which is updated each day with the latest information). The user can select the point in time which they want to view the line chart or default latest update day (today). Because of the number of value points on the line chart it is difficult to see the data values.

Is it possible to restrict the data values to only the selected (latest) date? This means there is only one value visible on the line chart (the last one).

Have tried using the 'Show Value' field but have not been successful. The 'TODAY' function works only if the default value is selected (today). Once the user selects a date prior to today the field does not show.

Thanks

Re: Display data point values on line chart

johnw — Wed, 18 May 2011 00:15:50 GMT

Add a second expression sourced from the first, but that only establishes itself for the dates you want. For example:

TotalSales = sum(Sales)
DisplayTotalSales = if(Date=max(total Date),TotalSales)

Only display data values on the second expression. Maybe I'm missing an easier way, though, as I don't see the "Show Value" on a line chart. Maybe I'm just missing something, or maybe it's a v10 feature (I'm on v9). You might be able to refer to max(total Date) in that field, or max(Date).

Re: Display data point values on line chart

Anonymous — Wed, 18 May 2011 00:32:07 GMT

Hi John,

Thanks for your response.

To make it simpler to understand what I am trying to achieve here is a screenshot. The

If the user selects a previous date the data is shown only up to that 'selected' date. I want the data value for the selected date to be displayed on the end of the line graph. The screenshot above uses the 'TODAY' (REFRESH_DATE=TODAY('DD/MM/YYYY') function. Obviously this does not work when a historical date is selected.

Cheers

Karl

Display data point values on line chart

Thu, 01 Sep 2011 21:42:32 GMT

This is a very important question. any answer?

Re: Display data point values on line chart

johnw — Fri, 02 Sep 2011 00:09:12 GMT

aryehgros wrote:
This is a very important question. any answer?

They're ALL very important questions. I bet there aren't very many people on this forum posting questions out of idle curiosity.

I answered this one. But it appears that either I misunderstood the question, or I did understand it, and Karl and you did not understand my answer. Or maybe my answer is wrong. I can think of cases where it wouldn't work, but nobody has said that either. As far as I can tell, nobody tried my answer.

What I suggested above is looking for the maximum date displayed on the chart, and putting a value above that data point. Karl said "the data is shown only up to that 'selected' date". So the maximum date displayed on the chart should be the 'selected' date, and therefore only that data point should have a data value above it. So what I suggested appears to me to be a solution to the problem.

Karl doesn't appear to have tried that solution. He appears to have tried using today(). I didn't suggest using today(), because "Obviously this does not work when a historical date is selected."

Now, in this specific case, it now appears that field "Refresh Date" contains the date for the data we want to display. So here, you could simplify the solution I gave to this:

DisplaySomething = if(Date="Refresh Date",Something) // display values on this expression only

But I have no idea if I'm even answering the right question.

Re: Display data point values on line chart

Fri, 02 Sep 2011 06:11:09 GMT

Hi Karl,

If possible, could you pls send us the sample.qvw file.

Thanks

vinod

Re: Display data point values on line chart

Fri, 02 Sep 2011 08:07:25 GMT

Hi All !

As per the John's Instructions i hv created an application .

John u r unbeatable!!!!

Re: Display data point values on line chart

SunilChauhan — Fri, 02 Sep 2011 08:20:43 GMT

may be this ( for credit of Erika and John)

please ssee the attched file

Re: Display data point values on line chart

Fri, 02 Sep 2011 14:40:50 GMT

John/Erica/Sunil,

thanks for your reply.

v10
I just discovered another solution. I used the reference-line. within the reference-lines dialog, you need to populate the expression & label. you should also set the "show label in chart" flag

For a combo-box it can be found on the Presentation tab. my needs are slightly different. I have a warehouse that i'm charting historical utilization vs capacity. the capacity (size of the warehouse) doesn't usually change, so on the capacity line it should display the value only once. The utilization number should display for each month, since the utilization amount constantly changes.

problems with my solution:
1) you can't dynically dictate the location of the text. it can be be hard coded with spaces.

Problems with your solution
1) The legend displays "Value" twice

Re: Display data point values on line chart

johnw — Fri, 02 Sep 2011 16:15:51 GMT

The reason the legend is displaying "Value" twice is that I stated one of the instructions VERY poorly. I'd said "Only display data values on the second expression." I really meant two things by this - on the first expression, do not display data values, and on the second expression, ONLY display data values. In other words, remove the checkmark next to "Line" for the second expression. I definitely should have written that differently.

The example is also using a variable, which I wouldn't bother with, and the example fails when you don't select any date. You can fix that by changing the expression to what I said to use, which in the context of this example would be this:

if(Date=max(total Date),Value)

Heh, nope. One minor error. It's displaying the nulls as '-'. I didn't expect that. So change it to a space, I guess:

if(Date=max(total Date),Value,' ')

That works. Thanks for building the example, Erika.

Edit: Oops, I didn't remove the variable like I suggested. I've updated the attachment to do so. Here's the new expression for Value:

sum({<Date={"<=$(=max(Date))"}>} Value)

Re: Display data point values on line chart

Fri, 02 Sep 2011 19:01:38 GMT

JOHN UR THE MAN!!!!!

thanks

Re: Display data point values on line chart

Anonymous — Wed, 26 Oct 2011 00:54:23 GMT

Hi all,

Have taken some time on this one (been on the backburner while other items were addressed) but I believe I have a simple answer. To display only the selected date on a line chart use the following script in the Show Value definition of the expression:

Cheers

Karl

REFRESH_DATE =GetFieldSelections (REFRESH_DATE)

Re: Display data point values on line chart

Tue, 14 Apr 2015 03:51:20 GMT

I have a variation of this post that this solution does not quite address and I would like some help.

If I chart all the possible values for Date, I want to display the value of last date in the line chart regardless of what date is selected. Both Karl and John's solutions seem to display only the value associated with the date selected.

Any thoughts on how to address.

Re: Display data point values on line chart

Mon, 27 Apr 2015 16:36:44 GMT

Hi all,

I need to display the value of last date in the line chart regardless of the date user selects. Do you have any I dea how I can do it?

thanks ,

Re: Display data point values on line chart

stevedark — Mon, 27 Apr 2015 22:50:42 GMT

You need a separate expression to select to show the value on the data points (as described above). Start by putting the identical expression to check you get the value on every point.

When that is working wrap the expression like this:

=if(DateDim = $(=max(DateDim)), [your current expression], ' ')

This assumes the dimension on the chart is DateDim, the square brackets and the code to be replaced with the expression you have on the line.

If you have date points beyond where you line ends (i.e. dates with a zero value for the line) you will need to apply Set Analysis to the max, e.g. $(=max({<MyValue={"<>0"}>}DateDim)) , this will ensure the date you show a value is a date that is actually displayed on the line.

Hope that helps.

Re: Display data point values on line chart

mmonfort — Mon, 25 May 2015 08:06:17 GMT

Hi guys,

Have just implemented this and I love how it is dynamic in providing me what I need. I currently use it to highlight the high, low and latest values over a specific period of time.

Problem I have is when some values - in this example, the low values - are multiple ones. If you look at the image it shows -18 in red as the low value which happens a few months in a row.

I want to know if there is a way to just show the latest 'low' value - ie just the latest -18.

Currently the code that calculates the Min Values score is =if(Rank(-avg([QMG Score]))=1,avg({<Period=>}[QMG Score]))

The dimension of the chart is just something called Period - eg 31/12/14

Re: Display data point values on line chart

stevedark — Tue, 26 May 2015 06:42:03 GMT

There are a number of different ways you can handle hitting the same value using the Rank function, using an extra parameter, one of these may help.

If not you could add the period to the score, but make sure it is a secondary factor in the rank by multiplying the score, eg:

Rank((-avg([QMG Score]) * 1000000) + Period)

Depending on whether you wanted the first or last point marked you could either add or remove the period value.

Hope that makes sense.

Steve

Re: Display data point values on line chart

antoniotiman — Tue, 26 May 2015 07:46:42 GMT

Hi Karl,

why don't use 'show value' in Expression -> + ?

Regards,

Antonio

Re: Display data point values on line chart

Wed, 15 Jul 2015 08:02:38 GMT

As suggested just modify the already suggested formular, and use the Show Value expression.

Re: Display data point values on line chart

Tue, 08 Dec 2015 10:29:48 GMT

Hi John / all

Please how can I display the last data point of the following expression?

=sum({1<Sale_Date={">=$(=Max(Sale_Date)-12)<=$(=Max(Sale_Date))"},Product={'Product 1'}>} Sales)/

Count({1<Sale_Date={">=$(=Max(Sale_Date)-12)<=$(=Max(Sale_Date))"},Product={'Product 1'}>} Sales)

Regards