2 different parameters on one field

[gillf]

Two linked tables (Contracts and Sales Invoices)
Formula 1 says "if invoice date is in ?Current Period Start to ?Current Period End, then show invoice values for that period. Else return 0
Formula 2 says the same but for ?Previous Period start and end.
I added the Sales Invoice a second time for the second formula but it returns 0. It's as if having evaluated the invoice date once it ignores the second try.
I linked the second table to the Contract table - is this where it falls down or am I doing something else wrong?

 

[pgtek]

Hi
try to have only 1 parameter
in period or between

cheers





pgtek

 

[gillf]

Sadly, I need the two parameters - one to show last period's figures and one to show this period - the report is to compare quarter sales over two financial years.

 

[dgillz]

Please post your record selection formula.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[lbass]

You could use a record select like:

{table.date} in {?start} to {?end} or
{table.date} in date(year({?start})-1, month({?start}),01) to
date(year({?end})-1, month({?end})+1,01)-1

This would give you the same period for the previous year, using only the start and dates of the current period as parameters.

-LB

 

[Ngolem]

You need to describe things better...post actual formulas not talked-through ones. You say your tables are linked...but don't show how.

Give us details ...don't make us guess

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[gillf]

Ccs_Quotes table linked to Ccs_S_Inv_Line table via Unique Quote Number, the only common & unique field on both tables.
2nd instance of sales table called Ccs_S_Inv_Line1. I'm experimenting with all kinds of links to this but again, the Unique Quote Number is the only link I can make.
Formula 1:
If{Ccs_S_Inv_Line.Ccs_Sil_Inv_Date} in {?Current Period Start} to {?Current Period End})
then {Ccs_S_Invoice_Line.Ccs_Sil_Net_Reten}
else 0
Formula 2:
If{Ccs_S_Inv_Line1.Ccs_Sil_Inv_Date} in {?Previous Period Start} to {?Previous Period End})
then {Ccs_S_Invoice1_Line.Ccs_Sil_Net_Reten}
else 0
The test contract I am using has 3 invoices - one in the previous period and two in current.
The first formula to return perfectly. I can also get the "previous" value into the second formula by linking the Unique Quote Number from the Contract table to Ccs_S_Invoices1 table, but then the values repeat for every invoice found.

 

[lbass]

You don't need the second instance of the Ccs_S_Inv_Line table, and it is causing the duplication of values. Just use the table once, and try my earlier formula. This should work as long as what you mean by "previous period" is something other than the same period a year ago. If you mean something different, please explain what the previous period might consist of compared to the current period.

-LB

 

[lbass]

I meant to say:

"This should work UNLESS what you mean by "previous period" is something other than the same period a year ago."

-LB

 

[synapsevampire]

Consider stating specifics, rather than "I added the Sales Invoice a second time for the second formula", such as where formulas are used and intent, and keeping in mind that you can neither add an invoice once nor twice, you add data, describing it should net meaningful results.

If you added these to the record selection formula, it will not differentiate the 2 periods, combinatively they return 2 "periods" of rows as one, and you must then later qualify each.

I'm always shocked that those trying to help don't ask what version of Crystal, database, connectivity, example data, expected output, where you're using formulas, etc.

A Running Total might handle this were you to use your date selection in the evaluate->use a formula

And then in the record selection formula use something that would limit rows to the entire data set required (2 periods).

My favorite sentence: "The first formula to return perfectly".

Not that I don't routinely post cryptic blurbs...

Such as this.

-k

 

[gillf]

Thanks everyone for your efforts. And yes, in any future post I'll try to supply the technical details, not numpty-speak.
Eventually found that the database structure is such that no formulae could have produced the results I need. An SQL View sorted it.
P.S. synapsevampire, very pleased that my nonsense non-sentence amused you. I find it quite easy to produce those under stress, if you're thinking of starting a collection.