| Author |
Message |
rkorb
Newbie
Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
|
   Posted: 10 Sep 2009 at 9:24am |
That is exactly where I'm going with it. Now I'm just trying to conditionally suppress the ones with a date of 1/1/1901 (the date I chose). I'm so close I can taste it... oh wait, that's just my coffee.
DBlank, you must be on the west coast, are you?
|
 IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Sep 2009 at 9:37am |
Chicago.
To conditionally suppress the @DOB
right click on it and select format Field
select Common tab
click on formula button next to Suppress
enter the formula:
currentfieldvalue=#01/01/1901#
Edited by DBlank - 10 Sep 2009 at 9:38am
|
|
IP Logged |
|
msnoshoes
Newbie
Joined: 10 Sep 2009
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 10 Sep 2009 at 9:55am |
|
I works fine in returning the string 08/01/209 (don't know how to get that leading zero to show in the 09). But when I add the DATE() I get the error message.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Sep 2009 at 10:03am |
|
Date(totext({LECUSD.CUS_ARR_DATE_MM},0)+"/"+totext({LECUSD.CUS_ARR_DATE_DD},0)+"/"+totext({LECUSD.CUS_ARR_DATE_CC},0)+totext({LECUSD.CUS_ARR_DATE_YY},"00",0))
|
|
IP Logged |
|
rkorb
Newbie
Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
|
Posted: 10 Sep 2009 at 10:18am |
|
The supress works great but I need to not include the supressed values in my average. Is there a way to not include values when doing an average? I can't do it in the select expert because I need the rest of the data in the report. Thanks again for all your help! You have showed me some great tricks!
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Sep 2009 at 10:48am |
There are a few ways to do conditionaly average (or sum or count or ...).
One is to use a variable formulas with a conditianal "if then" to skip items.
I use Running Totals usually because I find them easier.
I assume you want to get the average age.
Create a new RT as
Name=Average Age (or whatever).
Evaluate = use a formula as: @age >0
Reset = Never
Place in report footer to see results
If you need it per grouop yuou can reset it at the group level (one per group level is needed to be created)
Is that what you need?
|
|
IP Logged |
|
rkorb
Newbie
Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
|
Posted: 10 Sep 2009 at 11:02am |
|
DBlank you rock! I used the RTotals and they worked perfect! Why I didn't think of it is beyond me since I have been doing RTotal non stop for 2 weeks. Thanks again!
|
|
IP Logged |
|
msnoshoes
Newbie
Joined: 10 Sep 2009
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 10 Sep 2009 at 11:13am |
|
Thank you, Thank you, Thank you for fixing the leading zero. My formula does not work, but the leading zero was driving me insane.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Sep 2009 at 1:43pm |
|
no shoes, What is the problem with your formula?
|
|
IP Logged |
|
msnoshoes
Newbie
Joined: 10 Sep 2009
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 11 Sep 2009 at 12:12pm |
Date(totext({LECUSD.CUS_ARR_DATE_MM},0)+"/"+totext({LECUSD.CUS_ARR_DATE_DD},0)+"/"+totext({LECUSD.CUS_ARR_DATE_CC},0)+totext({LECUSD.CUS_ARR_DATE_YY},"00",0))
When I run a query using between 08/01/2009 and 08/02/2009 I get the error "Bad Date Format String.
|
|
IP Logged |
|
|
|
