topic Re: Select n-th value in a field in QlikView

Select n-th value in a field

Wed, 04 Dec 2013 18:10:55 GMT

Hi all,

I need help 🙂

I have a very simple table with 24 rows (one for each month from Jan 2013 to Dec 2014) and 3 columns (goal, projection, actual) that I am using for a line chart. The chart shows the goal and the projection lines for the the entire data set (Jan 2013 to Dec 2014) and the actual line for what we know (Jan 2013 to Nov 2013).

I do not want to add values to all data points because it would clutter the chart, but I want to tell the reader exactly where we are today (value of actual for this month) and where we are going (value of projection for next month). I plan to do this by adding a text box in the chart (presentation tab, "text in chart") and I am struggling with the set analysis syntax.

Because I have data points for the goal and the projection all the way to Dec 2014, I cannot use

='Actual: ' & sum({$<month={"$(=max(month))"}>} actual

and because the actual progression is not linear, I cannot use

='Actual: ' & max(actual)

So I thought about using the n-th item in my list of actuals. I know what the values I want to use are the latest in the actual column and the next for the projection column, which I can figure out with =COUNT(actual) and =COUNT(actual)+1, but I do not know how to say in my expression "use the (count(actual))-th item from the field named actual" and "use the (count(actula)+1)-th item from the field named projection".

Any suggestions?

Thanks!

Marina

Re: Select n-th value in a field

wallinpeter — Wed, 04 Dec 2013 18:34:25 GMT

Try this.....

It is a set statement within a set statement.

It selects the max(Month) from only the records that have an Actual value.

sum({$<month={"$(=max({$<Actual={">=$(=min(Actual))<=$(=max(Actual))"}>}month))"}>} actual)

Re: Select n-th value in a field

angelaecheverri — Wed, 04 Dec 2013 18:36:56 GMT

sum(goal) = Show you all the months

If(Aggr(Sum(actual), Periodo) >0, Aggr(Sum(goal), Periodo)) = show you just sum of goals when in the moth are actual data.

Re: Select n-th value in a field

Wed, 04 Dec 2013 18:54:33 GMT

Thanks Peter 🙂

The expresion returns a value, but the value is exactly the one before the one I want. What am I doing wrong?!!

Re: Select n-th value in a field

Wed, 04 Dec 2013 18:55:15 GMT

Gracias Angela 🙂

What do I use for "Periodo"? I tried NOW() and TODAY() but the expression returns a "-"

Re: Select n-th value in a field

jerem1234 — Wed, 04 Dec 2013 18:56:33 GMT

Maybe something like this:

=subfield(concat({1}actual, ','), ',', count({1}actual))

and

=subfield(concat({1}projection, ','), ',', count({1}actual)+1)

Hope this helps!

Re: Select n-th value in a field

wallinpeter — Wed, 04 Dec 2013 19:03:56 GMT

It is grabbing the Actual value for the wrong month?

Can you copy paste data and bold the the value you want returned.

Re: Select n-th value in a field

Wed, 04 Dec 2013 19:10:11 GMT

No!! My bad!

This works

Thanks Peter

Re: Select n-th value in a field

Wed, 04 Dec 2013 19:14:34 GMT

Thaks Jeremy.

The expression works too but it does not take into account selections (I forgot to mention: I allow readers to narrow down the year they want to see), so it always return the latest month, even if I select a different year. Is there an easy way to tell the expresion to "stick to the selection" insead of "use the entire data set"?

Also, if you have a moment, could you please explain how "subfield" and "concat" work in this logic?

Thanks again 🙂

Re: Select n-th value in a field

jerem1234 — Wed, 04 Dec 2013 19:35:28 GMT

For something like this:

=subfield(concat({1}actual, ','), ',', count({1}actual))

the 1 will ignore all selections. If you want it to go with the selections, then remove the 1's.

=subfield(concat(actual, ','), ',', count(actual))

The concat function will combine all your field values into one string with the delimiter of ',' in this example.

(On a side note: You can also use the third parameter of concat to establish the sorting of the string.)

Then subfield will go through this string and get the count(actual)-th item using the , as a delimiter.

So if you had a field like Fruit:

Then did concat(Fruit, ',')

your result would be:

Apple, Orange, Peach, Banana

Then subfield(concat(Fruit, ','), ',' , 3)

would return:

Peach

Hope this helps!

Re: Re: Select n-th value in a field

wallinpeter — Wed, 04 Dec 2013 20:28:04 GMT

Anytime.

Set Analysis is hugely powerful. Highly recommend you become proficient in it.

Attached is a tutorial QVW.....

Re: Select n-th value in a field

Wed, 04 Dec 2013 20:39:00 GMT

Sure does 🙂

Thanks again Jeremy!