Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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.
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.
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
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!