Knowledge Base Article

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
No CommentsBe the first to comment