Why is my formual returning an incorrect answer

[jcl5]

I have a formula which calculates dates in a quarter and is used in a running total in the report footer which gives the correct result as follows:

@Quarter
if({SALES.SALES_DATE}in Date (2003, 04, 01) to Date (2003, 06, 30)) then 'Q1' else
if({SALES.SALES_DATE}in Date (2003, 07, 01) to Date (2003, 09, 30)) then 'Q2' else
if({SALES.SALES_DATE}in Date (2003, 10, 01) to Date (2003, 12, 31)) then 'Q3' else
if({SALES.SALES_DATE}in Date (2004, 01, 01) to Date (2004, 03, 31)) then 'Q4'

#Q3
not isnull({CLIENTS.CLIENT_NO})
and
isnull({CLIENTCOM.CLIENTCOM_NO})
and
{@Quarter}='Q3'

//Returns 442


Then I created a new formula which calculates a second set of dates in a rolling quarter as follows:

@RQuarter
if({SALES.SALES_DATE}in Date (2002, 07, 01) to Date (2003, 06, 30)) then 'RQ1' else
if({SALES.SALES_DATE}in Date (2002, 10, 01) to Date (2003, 09, 30)) then 'RQ2' else
if({SALES.SALES_DATE}in Date (2003, 01, 01) to Date (2003, 12, 31)) then 'RQ3' else
if({SALES.SALES_DATE}in Date (2003, 01, 01) to Date (2004, 03, 31)) then 'RQ4'

#Q3R
not isnull({CLIENTS.CLIENT_NO})
and
isnull({CLIENTCOM.CLIENTCOM_NO})
and
{@RQuarter}='RQ3'

//Returns 442

This #Q3R total returns the same count as the #Q3 total even though the dates are completely different and I can't figure out what is wrong with it. Strangely enough it works ok for #Q1 and #Q1R - can you see anything obvious?

Many thanks

jcl5

 

[MColeman]

(2003, 10, 01) to Date (2003, 12, 31)) then 'Q3'
(2003, 01, 01) to Date (2003, 12, 31)) then 'RQ3'

Since Q3 is a subset of RQ3, is it possible that all of
the records in RQ3 also fall in the range of Q3?
(i.e. 10/1/2003-12/31/2003)

 

[lbass]

I think the problem is the overlapping date fields in your rolling quarter formula. If a detail meets the criteria for RQ1 or RQ2, it is assigned to those quarters and no longer evaluated for RQ3. Therefore, the only records being read for RQ3 are those in the period Date (2003, 10, 01) to Date (2003, 12, 31).

See bdreed's formula for rolling time periods in thread767-704190. I think this approach might be your best bet.

-LB

 

[jcl5]

I'm afraid I haven't a clue what to do with that formula as I don't understand what it is doing. How would I apply it to each of my rolling quarters? I am only looking to create distinct counts displayed in format.

Q1, Q2, Q3, Q4
RQ1,RQ2,RQ3,RQ4

Can you explain further?

Thanks

jcl5