Anyone have a formula for when you have a field va...
# suiteanalyticsx
XochisSketches
09/18/2023, 8:30 PMAnyone have a formula for when you have a field value change in the system notes, pull only the most recent change?
d
David B
09/18/2023, 8:48 PMyes, but it's not a formula. turn it into a summary type search, and use summary type MAX for your system note field(s). Then in the "when ordered by" field, select 'system notes : date'
David B
09/18/2023, 8:50 PMThere are ways to do this without resorting to a summary type search, but it gets complicated (using SQL functions)
👍 1
x
XochisSketches
09/18/2023, 8:50 PMLike how? I don't think for our use case the summary type search will work because we have some fields that need to show that can't be summarized
XochisSketches
09/18/2023, 8:52 PMThis gets us close, but we can't do a criteria on it to say 'pull only the ones where the value is '1''
XochisSketches
09/18/2023, 8:52 PMdecode(max/* comment */({systemnotes.date}) over (partition by {internalid}),{systemnotes.date},1,0)
d
David B
09/18/2023, 9:12 PMyeah, every search I have that uses this is a summary type search.
Where it's a non-summary type, the search results have duplicate rows...
z
Zoran R-DATAGRAM
09/22/2023, 7:44 AMtranslated to SQL, it requires two passes through data. One for "give me max/last" and second give me the one where id = latest.id
known problem for sql select, but possible with sql query... I guess that Analytics UI doesn't provide solution...
3 Views