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.
Ex:
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?
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
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.
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.
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!