Beast mode calculation: Last compared to past average?

EChampagne

Hi - I'm trying to figure out the calculation to identify an action needed. I know how to do in Excel, but still learning the SQL / Beast Mode equivalent.

I have a count of total items. If the count of total items from last week is 20% less than the average count of total items from the past 6 weeks, I want it to be marked somehow.

Thanks in advance for your help!

Sean_Tully

Is this to identify current state or would you need to know historically if the action was needed? If the former, I think you can use a fixed function in a beast mode to find the latest 6 week average and compare that to your current week count. If you need history, I think you'll need to use MagicETL to group by each week, then lag the previous six week totals so you can do the math to find the specific prior 6 week average for each week.

Sean_Tully

Is this to identify current state or would you need to know historically if the action was needed? If the former, I think you can use a fixed function in a beast mode to find the latest 6 week average and compare that to your current week count. If you need history, I think you'll need to use MagicETL to group by each week, then lag the previous six week totals so you can do the math to find the specific prior 6 week average for each week.