| Author |
Message |
jonman
Newbie
Joined: 20 Jun 2013
Location: Greece
Online Status: Offline
Posts: 3
|
  
Topic: Get a specified Value in order Date ?
Posted: 23 Jun 2013 at 8:01pm |
|
Please Help me i have the following table:
Date Item Price
1/1/2010 003 0.92
1/3/2011 003 0.92
1/4/2012 003 0.92
1/6/2012 003 1.16
1/3/2013 003 2.20
I want to get on the Group of item , the last price of item in a specified date range .
example the last price of 2012 : 003 last price = 1.16
|
|
Giannis M.
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 25 Jun 2013 at 10:47am |
|
if you filter out all the dates after 2012, then you could group by item, order by the date, and just display the price in the group footer (since the last record would be the correct price.
if you need to display all values, and you always want to get the latest from the same year, say 2012, you could write a set of formulas: (still grouping by price ordering by date)
group header (reset)
shared numbervar price :=0;
"" // will hide the 0 output
group footer (display)
shared numbervar price;
detail (increment)...actually store in this instance
shared numbervar price;
if YEAR({table.dateField}) = 2012 then
price := {table.priceField}
hope one of these work for you
Edited by lockwelle - 25 Jun 2013 at 10:47am
|
| IP Logged |
|
jonman
Newbie
Joined: 20 Jun 2013
Location: Greece
Online Status: Offline
Posts: 3
|
Posted: 25 Jun 2013 at 8:59pm |
|
Thanks is working !!!!!
But if i take a date range is not :( !!
i change the detail formula to :
shared numbervar price;
if {table.datefield} in DateTime (2009, 12, 31, 0, 0, 0) to DateTime (2013, 03, 31, 0, 0, 0) then
price := {table.pricefield}
is not working !!
|
|
Giannis M.
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 27 Jun 2013 at 4:37am |
|
I'm never sure about the range function...the syntax looks correct.
Have you tried:
if {table.datefield} >= DateTime (2009, 12, 31, 0, 0, 0) and {table.datefield} <= DateTime (2013, 03, 31, 0, 0, 0) then
It's worth a try
|
| IP Logged |
|
|
|
