I am trying to use `RANK` `DENSE_RANK` and `KEEP` aggregate SQL Expressions. I have a saved search that is looking at the SO. I want to pull in the first IF created off of the SO. I know can just `GROUP` the sales order and `MINIMUM` on the Fulfilling Receiving transaction, but because of some alternative factors I can't do it this way.
In the results of the search I am able to get the desired results. If you see the last column in the 'Results from running search' image, I use the `DENSE_RANK` function in order to rank which IF came in first. Now I want to use that formula in order to filter over the summary criteria of the search.
I tried to wrap the around in a case statement like this `CASE WHEN (DENSE_RANK() OVER (PARTITION BY {tranid} ORDER BY {fulfillingtransaction} ASC)=1) THEN 1 ELSE 0 END` (see image 4). This works in the results section.
Why doesn't this work when I do it in the summary criteria, or how else would I use it? Up for any combo of KEEP or DENSE_RANK that works. Thanks!
Criteria
[ATTACH=JSON]{"data-align":"none","data-size":"custom","height":"193","width":"414","data-attachmentid":430938}[/ATTACH]
Results -- last formula `CASE WHEN (DENSE_RANK() OVER (PARTITION BY {tranid} ORDER BY {fulfillingtransaction} ASC) = 1) THEN 1 ELSE 0 END`
[ATTACH=JSON]{"data-align":"none","data-size":"custom","height":"129","width":"554","data-attachmentid":430939}[/ATTACH]
Results from running search -- Formula works, see last column
[ATTACH=JSON]{"data-align":"none","data-size":"custom","height":"154","width":"652","data-attachmentid":430940}[/ATTACH]
Summary Criteria --- this causes it to error out and please contact NS. I tried sum as well as count. Value is equal to 1. And putting in on the regular criteria
[ATTACH=JSON]{"data-align":"none","data-size":"custom","height":"75","width":"704","data-attachmentid":430941}[/ATTACH]