BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Josh_Breault
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
FredrikE
Rhodochrosite | Level 12

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

View solution in original post

1 REPLY 1
FredrikE
Rhodochrosite | Level 12

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

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 657 views
  • 1 like
  • 2 in conversation