before and after sales

[Josh_Breault]

I am having a hard time finding a solution to this question, therefore I need your help.

I have a table that has a record for each invoice #.  Each record will also have other fields such as cust# sales posted date, sales, etc. etc.  I have joined this table to my customer table that has a date that denotes when this customer became a loyalty customer.  So now my output table, for each record, has a posted date and the loyalty date.  I'm trying to determine the sales for each customer prior to and after the loyalty date.

I have tried derived items (maybe i'm not using the correct one) and i've also tried various formats for formulas, such as

If (loyalty date >= posted date), 1 0

This lumped every customer as a 1 or a 0, but did not separate them out at the invoice level.  Maybe i need to do something to the dates in the calculated item, i'm not sure. 

Any advice?

[FredrikE · Accepted solution]

Maybe you can create two new calculated variables for sales (i call it salesVar here) like:

Sales_after:

if loyalty date >= posted date then salesVar else 0

Sales before:

if missing(loyalty date) then salesVar else 0

//Fredrik