formula to count records between two dates

[chomzky]

Hi THere,

I am creating a manual crosstab and would like to count the number of records that occur between two sets of dates. I am using the following formula

if month({gameauditstartdate}) in [1] then
({gameauditstartdate})
else
0

I would like to be able to select the more than just the month as the data goes back a few years. Can someone point me in the right direction please.

REgards,

K

 

[IanWaterman]

Cretae two date parameters {?StartDate} and {?EndDate}

then in your formula

if {gameauditstartdate} >= {?StartDate} and {gameauditstartdate} <= {?EndDate} then
1
else
0

Then use a sum summary of this formula, I am suprised your formula works as yo are mixing dates and numbers in your if ..then...else

Ian

 

[chomzky]

thanks for your reply Ian. I created two formula's and was able to draw the inforamtion for Quarter 1. When I created a similar formula (different min and max date values) to extract information for Quarter 2 I get the same summary information for quarter 2? I am inserting a summary field then dragging the field up to the header section to diplay the results.

Formula
frmAuditCountStartQ1
minimum(Calendar1stQtr)

frmAuditCountEndQ1
maximum(Calendar1stQtr)

then use the formula

{GameAudit.GameAuditStartDateTime} >= {@frmAudit_Count_Date_Start_Q1} and {GameAudit.GameAuditStartDateTime} <= {@frmAudit_Count_Date_End_Q1}

Can someone enlighten me please

 

[IanWaterman]

If you are grouping by quater you do not need any formula. Just add a distinct count to Quarter Group Footer. You do not need the parameters either unless you want to set a date range for the whole report.

Add a new Group based on the field {GameAudit.GameAuditStartDateTime} in the third drop down window select Quarter.

Ian

 

[CoSpringsGuy]

your formula could read
if {GameAudit.GameAuditStartDateTime} in [minimum(Calendar1stQtr) to maximum(Calendar1stQtr)] then 1 else 0
avoiding the other two formulas you created but regardless yours works

then for second qtr
if {GameAudit.GameAuditStartDateTime} in [minimum(Calendar2ndQtr) to maximum(Calendar2ndQtr)] then 1 else 0
then for third qtr
if {GameAudit.GameAuditStartDateTime} in [minimum(Calendar3rdQtr) to maximum(Calendar3rdQtr)] then 1 else 0
then for fourth qtr
if {GameAudit.GameAuditStartDateTime} in [minimum(Calendar4thQtr) to maximum(Calendar4thQtr)] then 1 else 0





_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

actually ... what the heck am I thinking!? I got caught up in your code

1st quarter
if {GameAudit.GameAuditStartDateTime} in (Calendar1stQtr) then 1 else 0

etc for 2nd, 3rd and 4th

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[chomzky]

thanks for the assistance. I tried these formula's out on a crosstab and they worked great.

Here is my setup:
I have two tables:
Game Audit Table
Fields:
GameAuditGUID (primary key)
GameAuditStartDateTime(date field)
Area (ie Table Games, Slots etc)
Section (ie blackjack, roulette etc)

GameAuditViolation
GameAuditGUID (key) (There could be many Violations for each GameAuditGUID)
ViolationDateTime (Date)
Violation (text)

I would like to add the quarters as columns and the audit section or area as the row. The report would look something like this:

[ul]
[li] Q1 Q2 Q3 Q4[/li]
[li]BlackJack 35 44 50 77[/li]
[li]Roulette 55 43 66 88[/li]
[li]Clerarance 22 42 55 88[/li]

[/ul]The report would be based on a rolling one year period (which means I will have to supply specific dates) when extracting the quarters.
There has to be some conditional formatting for individual filed which is the reason I am leaning towards a manual crosstab.

I would eventually like perform a calculation on the number of violations for each audit area or section.
ie: Audit Count / Number of Violations/

any furhter help is appreciated....

I can pay slot coupons for anyone working in my town !!

 

[CoSpringsGuy]

try creating your crosstab like this

in crosstab expert
For columns use {ViolationDateTime} --in group options select the dropdown box under The column will be printed... select for each quarter
for rows use {Section} (or {area}.. im not clear on that)
for summarized fields use {GameAuditGUID} and change summary to count





_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection