Filter Oldest Date For Each Product In A Pivot Table
Question:
I'm attempting to select the First Time (day) each product in a list was ordered.
Example:
Product A | 10/10/2018
Product A | 10/30/2018
Product B | 09/01/2016
Product B | 10/10/2018
Result:
Product A | 10/10/2018
Product B | 09/01/2016
Answer:
You can achieve that in two ways:
1. Create a Custom SQL Query on ECube, that will extract first date per product, and then just display it in Pivot or Table; Query would probably look like that: SELECT [ProductID], MIn([Order Date]) from Orders GROUP BY [Product ID]
2.Create a custom field on your order table in ECube and store in it date converted to int. Now on Pivot table add to rows Product ID, and Date. Then filter Date and set filter to Ranking > Bottom 1 based on field that converted date to int
Updated 10-15-2021
