Formula Calculation Based on Effective Dates

[TonyD88]

I have a Sales table (CustCode, InvDate, InvNumb, InvAmount) that links to the Commission table (CustCode, CommissPerc, EffectiveDate) via CustCode. The report is for commissions by customer, and I am trying to calculate the commission percentage by multiplying the Sales_InvAmount by the Commission_CommissPerc field. However, if the Commission table has more than 1 line per CustCode, it creates a duplicate line for each EffectiveDate.

For example, CustCode AB45 has an invoice dated 02/04/2013 for $1,000 in the Sales table. There are 3 lines in the Commission table for CustCode AB45 (CommissPerc 0.015 EffectiveDate 04/19/2012 -- CommissPerc 0.018 EffectiveDate 11/07/2012 -- and CommissPerc 0.020 EffectiveDate 02/25/2013). I would like the formula to compare the Sales_InvDate to the Commission_EffectiveDate and only return 1 line using the 11/07/2012 CommissPerc of 0.018 (because the Sales_InvDate of 02/01/2013 is AFTER Commission_EffectiveDate of 11/07/2012 but BEFORE Commission_EffectiveDate of 02/25/2013) multiplied by the Sales_InvAmount of $1,000.00 for a value of $18.00. How is the easiest way to do this?

Tony

 

[Madawc]

You need to group by Effective Date. Maybe also use variables to do the calculation just for the group.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[IanWaterman]

Unfortunatley Crystal will not allow you to join with less than and greater than joins on same field.

You will need to build a view on database or a command in crystal which defines the data range of your commission

Your commission table currently looks like
Custcode CommissPerc EffectiveDate
1 0.015 04/19/2012
1 0.018 11/07/2012
1 0.020 02/25/2013

The view needs to generate a rersult like

Custcode CommissPerc EffectiveDate EffEnd
1 0.015 04/19/2012 11/06/2012
1 0.018 11/07/2012 02/24/2012
1 0.020 02/25/2013 current sysdate

You can then join invoice date to this view using greater than and less than joins on the two date fields

Ian

 

[TonyD88]

Thanks for the suggestion. I had not thought of that.

Tony