Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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
ooo! Have you got any examples of this please, im fairly new to set analysis
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?
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.
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 ?
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