Returning values within a set range

[#1]

Good afternoon, I need to do some averages on some data but I only want to use the most recent date and a date thats as close to as a year ago as possible. i.e.

28.05.2012

24.02.2012

10.10.2011

08.07.2012

29.05.2012

26.05.2012

Above is a range of dates using the most recent date and a date up to a year ago(as close as possible) ignoring all the data inbetween.

How do I go about this?

Kind regards.

Paul.

[#2]

Hi, in the scrip you can make a table to load top 10 o 20 dates, the data source table must be ordered by date.

[#3]

Ermm that really didnt help me at all. Im needing help on scripting, not on ordering the data that I already have, I am needing to get the 1st and last date value within a range of a year from today.

Paul

[flipside]

Hi Paul,

You could add a flag field to your load script setting dates within past year to 1 and others to 0, then specify this flag in a set analysis statement for your averages. That's how I normally do running YTD calculations.

flipside

[#5]

ooo! Have you got any examples of this please, im fairly new to set analysis

[#6]

at the moment im using

if(max(Meter_Reading_Date)<=today(),Meter_Reading)

(as an expression)

its giving me randomly correct and incorrect and even sometimes blank data

[#7]

I've gotten FirstSortedValue(Places_Before_Decimal_Point_In_Meter_Reading,-Meter_Reading_Date) to work out the latest meter read date.

How do I go about getting a meter reading thats the furthest back inside a year?

[flipside]

Here is something that should help.  I wasn't sure if you wanted the oldest date within the year, or the date nearest to 1 year ago, so have added flags in the load script for both options.

[#9]

This looks very good, a little complex for what im trying to achieve. I currently use this to obtain the most recent date.

FirstSortedValue(Places_Before_Decimal_Point_In_Meter_Reading,-Meter_Reading_Date)

I use this to get the oldest date:-

FirstSortedValue(Places_Before_Decimal_Point_In_Meter_Reading,Meter_Reading_Date).

im needing an adaptation of this to get the oldest value up to 425 days of age. Do you now how to achieve this ?

[flipside]

I would probably use variables to make this easier.

1) Set up a variable to hold the value of the comparison date (Latest date minus 425)

     vDateCompare

     Definition: =max(Meter_Reading_Date)-425

2) Use this expression

     =date(alt(max({<Meter_Reading_Date={'<$(vDateCompare)'}>} Meter_Reading_Date),min(Meter_Reading_Date)),'DD/MM/YYYY')

This can be broken down into 3 parts as follows ...

i) Return the latest date where the date is less than the variable

   =max({<Meter_Reading_Date={'<$(vDateCompare)'}>} Meter_Reading_Date)

ii) If there are no dates, you need to return the oldest date available so wrap an alt statement around it

   =alt(max({<Meter_Reading_Date={'<$(vDateCompare)'}>} Meter_Reading_Date),min(Meter_Reading_Date))

iii) Finally, if this is to go into a text box, format the expression to a date

   =date(alt(max({<Meter_Reading_Date={'<$(vDateCompare)'}>} Meter_Reading_Date),min(Meter_Reading_Date)),'DD/MM/YYYY')

flipside