LAG & IFELSE

Q&A
, , ,
1

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.

sumOver(
ifelse(
lag(sum({hours}), [truncDate(‘WK’, date) ASC], 1, [warehouse]) >= .0135, 1, 0
),
[warehouse])

snip

1 Like
2

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:
image

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.

sumOver(
ifelse(
lag(sum({hours}), [truncDate(‘WK’, date) ASC], 1, [warehouse]) >= .0135, 1, 0
),
[warehouse], PRE_AGG)

Let me know if any of these suggestions help you achieve the expected output!

1 Like
3

@WLS-DM Thanks for your response.

I’m getting “Execution order mismatch: PRE_FILTER calculations can’t have PRE_Agg operands.” error.

error

1 Like
4

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.

This is what my calculated field looks like:

ifelse(
lag(sum({hours}), [truncDate(‘WK’, date) ASC], 1, [warehouse]) >= .0135, 1, 0
)

Let me know if this helps!

5

Thanks @WLS-DM,

It’s still not quite adding it up for the total how I want it too once I bring in more warehouses into the visual

formula

Result

I did find a workaround though using this formula:

ifelse(sumOver(ifelse({balance_month} = ${pPriorMonth}, {fault_hours}, 0), [whid], PRE_FILTER) /
sumOver(ifelse({balance_month} = ${pPriorMonth}, {total_hours}, 0), [whid], PRE_FILTER) >= .0135, 1, 0)

working result

2 Likes
6

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.