Filter for first time use and onwards

[chrpap185]

Could be a rookie question but I wouldn't know...

We have a service product that has certain independent features the customer can choose from in every transaction.
The feature used is recorded in our database in a single field called "Feature" and can take values (A, B, C)
We have launched a new feature D to the product.

Using the report studio I would like to examine the allocation of "Feature" for each customer ("Customer_ID") but only for those customers that have used feature "D", from the Date ("Transaction_Date") that they used feature "D" for the first time onwards.
The "Transaction_Date" is in numeric YYYYMMDD form
I have created a list query with the following fields
[Customer_ID]
[Transaction_Date]
[Feature]
[NoTransactions]
[Customer_Date] = [Customer_ID] || [Transaction_Date]

I am trying to create a filter like [Customer_Date] >= minimum (filter([Customer_Date], [Feature]='D')
but it only returns feature D transactions.

Am I moving to right direction?
Could you recommend something else?

Thank you very much

[Nikhil Shenoy]

You want the first date from when that Customer ID started using Feature D?

[chrpap185]

I want the his transactions from the first day he used feature D until today

[Nikhil Shenoy]

try to convert the date column using Cast function
CAST ([Fieldname]  AS DATE )
and then try the filter which you are trying to apply.

I guess since the date column is in numeric format, its creating problem.

[hespora]

this is by no means a rookie question, don't worry. here's how to do it (on relational):

- create a data item "date check" as such:

Code Select Expand


minimum(
  case when [Feature] = 'D' then [Transaction_Date] else null end
  for [Customer_ID]
)


- add a filter [Transaction_Date] >= [date check]

- in its properties, set this filter to After Auto Aggregation

[chrpap185]

I am sorry for the late reply but it was just now that I managed to work on it again.

Although your solution seems right if I understood the expression's logic correctly and does not give any error messages as what I was trying before, the report never finished running. Even when I scheduled it and came back the next day to check on it nothing was saved.

Could this mean that my provider (it is a third company) does not have the necessary resources to perform the calculation? If I use "before auto regression" option it wouldn't be correct? I am asking because I think this is the cause of the problem ([Customer_ID] is a few millions).

Thank you for your help

[hespora]

You're correct in your assumption, applying this filter before auto aggregation will not yield correct results. Think about what it does:

- on every row in your dataset, [Feature] is compared to 'D', and if it is, it's using [Transaction_Date], otherwise null.
- now, it takes all those values, groups per customer, and looks at the lowest value per customer.

If, by applying a filter before auto aggregation, you did not include every row in your dataset, the calculation would not be able to consistently find the correct minimum, as that minimum row might be filtered out.