Help :Date prompts macro calculations

[ELISAMENDES]

Hi experts,

I am a beginner on Cognos so I would appreciate your help with my "rookie" questions.

I would like to filter transactions to only show the last transaction date per customer. As an example, if a customer has 4 transactions, on the 01Jan,02Feb, 15Marc I would only select the 15 March. And create a new variable with this filter calculation, let's say LastTrx.

I would like to link this query to a date prompt, where I can select a data range. I need to add further calculations to this query, so if a user selects a data range from 01Jan2014 to 31March2014(data prompt) the query will select the 1096 days before the starting date (01jan2014-1096days) to 31March2014=Jan2011 to Marc2014.

Finally I need to create a new variable based on these values calculation. If a last transactions was made between 364 days and 0 days Segment=Active; >365 Segment<=1095=Inactive; >1096=Lost.

The final table will have customer_id, LastTrx and Segment columns.
So I think the steps will be like this:
-   Date prompt: User selects a data range from 01Jan2014 to 31March2014
o   I am able to run this data prompt range but I don't know how can I create and select only the last transaction;
-   Date Filter: Filter data will calculate the 1096 previous to the starting date to the end date =(01jan2014-1096days) to 31March2014=Jan2011 to Marc2014;
o   How can I create this query linked with the data prompt?
o    I am able to select a date range on the date prompt, but it gives me error when I try to do calculations on data dimensions – as an example subtract 1096 days.
-   SQL Query: Calculate the last transaction of members during this period and create the final variable label Segment.
o   How can I link this query with the previous ones?

Sorry I know it is a long list. I would appreciate your comments/help in any of the above questions.

Elisa

[BigChris]

Hi Elisa,

Welcome to Cognos. This is only a partial answer, but your date filter can probably be built along these lines. Let's say you've got two prompts for your start and end dates. Your filter could look something like this:

[YourDateField] between _add_days(?pStart?,-1096) and ?pEnd?

C