How can I change the context to consider ALL the history

Question

Hello Oracle folks!

I've been trying to calculate if the purchase made by a customer this month (the context is given by a report filter) is his/her first purchase and count them, but I haven't found the answer nor ChatGPT, Claude or the docu to do so, here how my calculation is going, I tried Evaluate function (but honestly maybe not the right way) :

COUNT (DISTINCT

CASE

WHEN MONTH (MIN (TRAN_DATE BY ID) ) = MONTH ( CALENDAR_DATE)

AND YEAR (MIN (TRAN_DATE BY ID) ) = YEAR (CALENDAR_DATE)

THEN ID

ELSE NULL

END )

With this calculation, returns only the first purchase by client if the client purchased this month, no if is its first purchase.

Thank you in advance for the help!

Adrian Coronado

Gianni Ceresa · Answer

Hi Adrian,

Welcome to the Oracle Analytics community and forum.

Your question is related to a data source you are using, but you aren't providing any information on what that source is, what structure it does have, what product you are using etc.

For you all that is obvious as you are spending time trying to make it work, but here nobody else know anything about it.

If you provide more details, it will be easier to suggest possible ways for you to achieve what you are trying to do.