topic Last 10 entries in QlikView

Last 10 entries

Tue, 27 Sep 2011 13:05:37 GMT

I would like to calculate the avarage of the last 10 entries (date_value). I have a Date, and an extra value attached to that date.

so 24-09-2011 with value "123".

Thanks to an other topic, I thought I found a solution after modifications.

=IF(

aggr(

rank(total

aggr( (date_value),date_value)

,0,1)

,date_value)

<=10,

aggr(

rank(total

aggr( (date_value),date_value)

,0,1)

date_value))

But I noticed this wasn't the solution. It shows the last 10 entries in a table, but I'm planning on creating a Gauge which should display the avarage of these last 10 items.

Instead, if I place avg() in the above code, it shows the avarage of all values together. Not just the most recent 10 dates.

What expression should I use to get the last 10 entries (date), so I could calculate with just these 10 entries?

Thanks in advance

Last 10 entries

swuehl — Tue, 27 Sep 2011 14:47:16 GMT

Hi,

I think a

=avg(aggr(if(rank(date)<=10, date_value), date))

should give you the average of the values for the last 10 dates (date is a Date format, with a numerical representation, right?).

Regards,

Stefan

Last 10 entries

Tue, 27 Sep 2011 15:13:06 GMT

hmm how should I implement this?

Delete all the code I had and just put this code in place as calculated dimension?

Because I get no value then.

Maybe I didn't explain it correctly,

but the dates all have a value.

So for example (5 dates for ease)

Date | Value

27-09-2011 | 123

13-09-2011 | 119

24-08-2011 | 116

22-08-2011 | 122

01-08-2011 | 110

The avarage should be the value of those (123+119+116+122+110) /5 = 118

The dates come from an Excel file where they have a Date Format.

Load DD as date_value

Last 10 entries

swuehl — Tue, 27 Sep 2011 15:30:15 GMT

Try putting above expression in a Text box.

your dates needs to be recognized as such by QV, i.e. a num(date) e.g. as calculated Dimension should return numerical values.

Re: Last 10 entries

Wed, 28 Sep 2011 06:32:17 GMT

I really can't get it to work.

Just for information.

In my Original Excel sheets I have column A which has different Dates in it

e.g.

27-09-2011

13-09-2011

24-08-2011

22-08-2011

01-08-2011

they are read in the following way:

Load DD as date_value

In QV it shows the dates nice and correctly if I select =date_value.

It's shown the exact same way, so

27-09-2011

13-09-2011

24-08-2011

22-08-2011

01-08-2011

If I use your code, should I leave "date" or should I create someting else from it.

This because if I leave it date, the date_value doesn't get the redlike color.

I click new Chart and place this code in it via Calculated Dimension

I get an "// error in calculation dimension" error

As expression I then use =avg(Rv) (which is that value of which I want the average, those 123, 119, 116 etc.)

So I guess I'm doing something wrong?

Tried all different things with this piece of code, but till now, no success.

Re: Last 10 entries

swuehl — Wed, 28 Sep 2011 18:08:54 GMT

I am sorry, I assumed date_value is the field name for the value associated with that date, if it is your date field, and RV your value field, you need to change to

=avg(aggr(if(rank(date_value)<=10, Rv), date_value))

Put this expression in a textbox or use it as expression in your gauge chart (not as calculated dimension, why do you need a calculated dimension here?).

I attached a sample, I hope you are able to open the file and not working on a personal edition.

Your date_value needs to be recognizes as Date type, as said, and unfortunately it will display correctly even if not (because it might be interpreted as string then).

I assume the date format you have given above is also set in your script with

SET DateFormat='DD-MM-YYYY';

If so, your date_value is almost certainly a Date type. To check, you could create e.g. a year field by using

LOAD

...

year(DD) as Year,

...

From ...;

This only works correctly if DD (you need to use the original field name from excel here, date_value is yet unknown) is recognized as a Date.

Hope this helps,

Stefan

Re: Last 10 entries

Thu, 29 Sep 2011 06:30:38 GMT

Thanks Stefan,

This was the solution.

I believe I tried this modification once, but I had an dimension added to it.

I deleted the dimension and indeed the correct value showed up.

Thanks a lot!