I have a report that has a structure field, and two date fields. one of the date fields {date.field1} is calculated field and spans a certain period of time and the other {datefield2} is a database field and spans across a broader time frame. I plotted a line chart based on the dates that will count the structures. {date.field} = 01/01/2009 to 05/30/2009 and {date.field2} = 01/01/2009 to 11/25/2009 and i do a running total for the structure by doing a distinct count. The problem is when i put the fields in the report, after {date.field1} has exceeded the date, there is still a count for the structure beyond that, so when i plot the chart expert, in the show values, there are two RT counts and 'on change of' has a formula which is :
DateX:
if {date.field1} in date (2009,01,01) to date (2009, 11,25) then {date.field1} else if {date.field2} in date (2009,01,010) to date(2009, 11,25) then {date.field2} else Date(0) .
One is a RT (RT1)based off the {date.field1} and the other (RT2) off {date.field2} but it seems like when i plot the line chart with DateX on the horizontal axis, there are counts for the {date.field1} even when the {date.field1} ends at '05/30/2009', so it seems like the {date.field2} is forcing the count for the RT1 based off {date.field1}. How do i prevent the RT1 for the {Date.field1} to stop showing values after the {date.field1} has reached its maximum value? The RT2 for {Date.field2} is perfectly fine with the right values, but having trouble for the {Date.field1} RT1. Please any help will be appreciated!!
Edited by moontide - 09 Aug 2011 at 2:17am
IP Logged