Hi all - I'm trying to filter a summary search to ...
# suiteanalyticsg
Gabriel Folstein
05/03/2024, 9:51 PMHi all - I'm trying to filter a summary search to only lines with the largest Line ID in each transaction. I was able use the following formula in the Result and in Highlighting to see which rows I want to filter to, but encounter an error whenever I try to add it to the Criteria. Can anyone see what I'm doing wrong or have another approach that would work?
Copy code
CASE WHEN {line} = MAX/* comment */({line}) OVER (PARTITION BY {internalid}) THEN 1 ELSE 0 ENDš
1
Gabriel Folstein
05/03/2024, 9:54 PMIt is not currently a Summary Search, but I thought it'd be important to mention in case the timing of when Analytic Functions are executed was an key factor. The screenshot is what I have so far - what I want is to filter to only the green rows; then I'll remove my formula from the results and apply summary types to the other columns
n
Netsuite Tragic
05/03/2024, 10:26 PMHave you tried using Order By column on the results?
Netsuite Tragic
05/03/2024, 10:27 PMThat is intended to allow you to show a column result which matches the highest (or lowest) of another column
g
Gabriel Folstein
05/03/2024, 10:30 PMThanks for the suggestion. I did try that, and it ultimately didnāt work for my use case. Iām working with inventory cost evaluations which seem to be an odd record type. The cost components, sublist all share the same value for line ID and so it acts more like a revision; when new inventory cost revaluations are generated for an item at a location on a given date, all of the line IDs are incremented by one. My goal is to filter my results to the latest ārevisionā.
Gabriel Folstein
05/03/2024, 10:32 PMOnce I have the value from the latest revisions, I need to sum those values
n
Netsuite Tragic
05/03/2024, 10:33 PMIs there a Last Date Modified?
g
Gabriel Folstein
05/03/2024, 10:35 PMI believe so, but all lines are returned including previous revisions
Gabriel Folstein
05/03/2024, 10:36 PMIn other words, I may open the record and have four lines in my cost components but when I load the search results, thereās the same for cost components with line ID one and again with line ID two
n
Netsuite Tragic
05/03/2024, 10:39 PMCan you try is Date Last Modified as a column set to Max. Then use Ordered By as Date Last Modified
g
Gabriel Folstein
05/03/2024, 10:53 PMI donāt think I can use the ordered by column because I need to Sum my āMax when ordered by line IDā
Gabriel Folstein
05/03/2024, 10:53 PMthatās what sent me down this road of using the analytic version of Max
š 1
āļø 1
m
MGBC
05/04/2024, 2:15 AMI donāt think you can use these aggregate functions in criteria. Thereās a support article about DENSE_RANK and RANK. They are not allowed in criteria of summary searches
g
Gabriel Folstein
05/06/2024, 12:59 PMThat was my suspicion as well, analytic functions are one of the last to be executed. Adding ideas on how to only sum values with the largest line ID's in a summary search?
m
MGBC
05/06/2024, 2:05 PMYou could add a custom column checkbox called ālast lineā. Then use a script to check that box on all your transactions for the largest line ID. The make a saved search where ālast lineā = T and then you can summarize that
Or
You would have to make a search of all the largest line IDs then sum in excel
Or use a connected BI tool. But either way it would be offline
g
Gabriel Folstein
05/06/2024, 2:06 PMThanks for the ideas. If you have a second, check out my use case:
https://netsuiteprofessionals.slack.com/archives/C2A1ZEMF0/p1714775455082369?thread_ts=1714773071.515649&cid=C2A1ZEMF0
5 Views