I’m using the LAG function to pull prior week’s data, and use an IFELSE to see whether the prior week’s hours met a certain criteria. The math works at each row, but the TOTAL ROW is just showing 0. So I tried to include a sumOver over the whole calculation so that all the warehouses can be summed together to show how many met the critera, but the sumOver isn’t adding them up correctly at all.

Hello @Clayton_Barton, I am curious, in the field well of your visual, what is the total aggregation set to? Maybe that needs a slight adjustment:

Since you have a partition within the sumOver, you may want to set the total to sum so it will not partition the total as well. You may not even need to use a sumOver here, but rather sum since the table will contain the partitions.

Another suggestion would be to add a calculation level to your sumOver. I would add PRE_AGG at the end of the calculation to ensure the function runs correctly.

Hello @Clayton_Barton, it seems like the issue is coming from nesting the lag calculation within the sumOver aggregation.

What if you tried removing the sumOver then altering the total calculation within the field well? By returning just 1s and 0s within the ifelse statement, it should also sum the 1s in the table by the warehouse and date week partitions. Then you could also set the total aggregation to sum.

Hello @Clayton_Barton, I am glad you were able to find a work-around for this issue. Thank you for following up with the calculation you wrote to resolve this.

Let me know if you have any remaining questions and I can help guide you further.