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
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
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
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.
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
If so, your date_value is almost certainly a Date type. To check, you could create e.g. a year field by using
year(DD) as Year,
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,
Last10.qvw 130.0 K
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.
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