I have a situation where the following needs to be accomplished:
Previous natural QUARTER’s data are used to calculate a Factory-wide average and standard deviation of the conformance, and the resultant (average+3 standard deviation) value is used as a control limit and compared with the average WEEKLY conformance of each Line within the factory in the current natural quarter. The goal is to track week by week whether the average weekly conformance of a certain Line has exceeded the control limit in a line graph, and to view the control chart for different Lines and Factories using filters.
I tried avgOver or stdevOver(conformance, [Factory, truncDate(‘Q’,timestamp)], PRE_FILTER) to get the Factory-wide Quarterly conformance average & standard deviation for the current quarter, which came out as expected. However, I couldn’t figure out a way to get the number for the previous Quarter.
I also thought of replacing the conformance field in this calculation with a nested lag function, but did not know what to put for “lookup index”.
Update:
I tried to nest the lag function outside of the stdevOver:
lag(stdevOver(conformance, [Factory, truncDate(‘Q’,timestamp)], PRE_FILTER),[truncDate(‘Q’,timestamp) ASC],1)
and it’s now saying Table calculation attribute reference(s) are missing in field wells. I don’t know what I’m missing.
Hi @no_hair,
When you receive the error referencing ‘table calculation attribute reference is missing in field well’, it’s normally caused by your visual missing fields that are being utilized in the calculated field. I would suggest making sure all of your fields being used in the calc. field are also being utilized in your visual. If you’re using a table like your screenshot above, you can always hide those fields from the table if you’d prefer them not to be included.
Thank you for your reply.
I did put all fields in my calculations in the visual’s field wells. The inner calculation (stdevOver(conformance, [Factory, truncDate(‘Q’,timestamp)], PRE_FILTER) had no issue in the visual, but once I nested the lag function outside of it, the error popped up. I’m confused because there are no added fields in the lag function compared to the stdevOver - both calculations involve the Factory and timestamp fields, which are both in the field wells. I even added the conformance to the field wells but still received the same error.
Hi @no_hair,
Apologies for missing your last reply, if you tag the person you’re responding to, they’ll receive a notification that there’s been a response! Are you still facing issues here or were you able to find a work around in the meantime?
My thoughts here are that trying to use ‘PRE_FILTER’ with lag is causing the error as the two don’t work hand in hand. While this may not work, I’ve use a simple work around in similar situations: you could try creating a separate field/calc. field for your truncdate. Then, use that field when building your avgOver and stdevOver calculations instead, let me know if that allows you to build your previous quarter field!
