Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Graphical calculation view to get min and max of a date

Hi all,

I have a requirement that doesn't seem to work although the idea is simple.

I have a customer, and the customer is in a customer group.  The customer has transactions that occur on a date.

The requirement is that I need the Latest transaction date for every customer, and then the Earliest transaction date for every group.

In SQL that would be:

SELECT Group, min(TranDate) AS TranDate FROM (SELECT Group, Customer, max(TranDate) AS TranDate FROM TABLE GROUP BY Group, Customer) GROUP BY  Group;

In a Graphical Calc View the sequence should be:

Projection Node with Table

Aggregation Node on Group and Customer, and max on TranDate

Aggregation Node on Group and min on Trandate

Semantics Node with Group and TranDate.

But when I run this I always get the Group and MAX(TranDate).....

Any ideas?

Regards

Jack

Tags:
replied

HI Jack

this is a case for the "KEEP" flag.

Since you don't query for the CUSTOMER column in the final SELECT, the default behavior for the calc engine is to "optimize" it away.

Setting the keep flag to TRUE for "CUSTOMER" on the level of the first aggregation node should do the trick here.

Cheers,

Lars

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question