Logic for finding new transactions in Report Studio

[shawnmandel]

I am working in Report Studio 10.2.1 and I have a Clients table as well as a Transaction table.

I need to figure out, within a specified transaction date interval of 'from-date' and 'to-date', for example Jan-2015 to Oct-2015, how to return all new Clients with transactions within the specified date interval, but where no transactions had been registered prior to my 'from-date', i.e. Jan-2015.  Hence, the definition of 'new' Clients.

I've been struggling with this challenge for quite a while where no one has really been able to assist me as of yet.

Thanks in advance for any help on structuring the logic.

Regards,
Shawn

[cognostechie]

Hi

I do not have RS available right now but I can explain the process for this.

1.Create a query which will give you the First Transaction Date for every client (there are functions like 'minimum' in RS for this).
2.Create a prompt for From Date and To Date
3.Put a filter on the query to filter the transactions where the First Transaction Date is between the Date Range of the Prompt.

[shawnmandel]

Hello cognostechie,

For #1 -->  I am not sure what logic to use in order to extract the first transaction date for every client.  Would I do the following?

MINIMUM(transaction_date) FOR client

Would the above be a calculated query item within my query?

#2 & #3 are more straightforward. 

Thanks very much for your quick reply!!
Shawn

[cognostechie]

Hello Shawn -

Yes, it would be a calculated query item within the query and the expression would be like this:

minimum ( [Date] for [Client] ).  The 'for' would be inside the parenthesis.

Be careful because the Client and the Date might be from different query subjects and that would mean the joins have to be working between them.
The [Date] should be from  the Transaction table. Check the SQL , it will tell you whether it is doing it right or not.

For trial, create a List report with only two columns:

1> Query Calculation with the expression
2> Client ID or Name

Check the data and if it looks right then add step 2 & 3.