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

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

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
  • 656 views
  • 1 like
  • 2 in conversation