Scenario
User wants to see the last 4 dates of when an Item was used in a transaction.
In an Item Saved Search, if you use the Summary Type = Maximum for the Transaction: Date field, it will only show the most recent date, and there is no option to select how many recent dates that will be shown in the results of the search.
An Item Saved Search can be created for this requirement but we need to use MS Excel or something similar in order to Pivot the results.
This is because the formula used contains a window function DENSE_RANK() which is allowed only in conjunction with a SELECT list of query. In other words, although this function is a logical SQL expression, it cannot be used in Saved Searches either as Criteria or in the Results tab when combined with a Summary Type.
This behavior is still filed as Enhancement # 180201.
Solution
Here's the details of the Item Saved Search:
Note: You can also add other Criteria such as the Transaction: Type as necessary.
Formula used:
- Item Name: CASE WHEN (DENSE_RANK() OVER(PARTITION BY {name} ORDER BY {transaction.trandate} DESC)) <= 4 THEN {name} END
- Transaction Date: CASE WHEN (DENSE_RANK() OVER(PARTITION BY {name} ORDER BY {transaction.trandate} DESC)) <= 4 THEN {transaction.trandate} END
Save & Run the search, export it as a CSV or Excel file.
Open the file using MS Excel, then create a PivotTable and filter out the blanks.
The end result should look something like this:
Do you have a different formula that has the same functionality? Feel free to share them here!
-----
If you find this information useful, let us know by reacting or commenting on this post!