"Lagged" and "current" versions of the same field cannot interact in a calculated field

Hi all, I’m having a weird calculation problem that I’m hoping someone can help with:
I’ve created a calculated field using the equation

lag({hours}, [balancedate ASC],1,[name,department])

Which works. However, when I try to take the result and compare it to the “non-lagged” version of hours, I get an error. I tried several other ways, and seems that any interaction between “lagged” and “non-lagged” versions of the same field results in that error.

ifelse(lag({hours}, [balancedate ASC],1,[name,department]) < {hours}, true, false)

Errors out. The message is “We encountered an error trying to save your calculated field. Please try creating it again.”
Is there a workaround here? Am I missing something?

1 Like

Just wondering if anyone could provide any insight here. Unfortunately, doing the lag() in one calculated field and then using it in a second one doesn’t work either

lag_hours: lag({hours}, [balancedate ASC],1,[name,department])

Even something as simple as
{lag_hours} + {hours}
fails with the above error.

1 Like

Hello @nichbenn, welcome to the QuickSight community!

I am curious, are you using this calculation in a table where the data is going to be displayed at the name/department level? The main issue with your function, is you are comparing an aggregated field and a nonaggregated field. If the data is being displayed at the correct partitions, you could aggregate the comparison hours by min, max, or sum, and it should then be able to be compared to the result of the lag function.

Let me know if that helps!

Hi Dylan, thanks for responding. It seems odd to me that QS would consider lag (and presumably lead) to be aggregate functions. They do not ‘combine’ rows and are not considered aggregates in other environments (for example, Redshift SQL, where you are not required to group by other columns when utilizing them). I was able to work around this error by simply adding an additional column to my source query. If this is intended behavior, then I’m happy to consider this answered/resolved, although it is an unfortunate limitation. Thanks again.

1 Like

Hello @nichbenn, yeah I am not 100% sure why the lag function is considered an aggregation, but this is the current functionality of QuickSight. I appreciate the follow-up response, and I am glad it is working, even if it isn’t exactly how you were expecting. Thank you!

1 Like