These sites do not do anything as complex as the formulas I need. I still need help!
I did not fully catch the gist of the AvgGrowth or YOY,
but I made an attempt at the alert.
Flag Most Recent Month:
[New Date (Months)]=DATETRUNC('month',DATEADD('month',-1,TODAY()))
Flag Month with Traffic of Zero:
IF SUM([Traffic]) = 0
THEN 1 ELSE 0 END
Any 0-months in prior 12:
WINDOW_SUM([Flag 0 or Null Traffic],-12,0)
So Critical Growth Alert would be:
THEN "Alert Growth"
Please see sheet Combined Alert in the
workbook v18.1 attached in the Forum Thread.
276166table.twbx 271.3 KB
This was very helpful!!! I apologize it took me so long to get back to you, as I was sidetracked with other work. You solved the first step of my problem.
I have built on what you taught me and come up with more questions if you're willing to answer them. Back to the same example though in TableCalcs2...
[AvgGrowth] attempts to use the WINDOW_AVG function to average the last 12 months of [Traffic-YOYGrowth], yet ignore when the site is down for maintenance.
The data for Site "TSA" in the [Billing Region] "AeroThai" is a problem for me. In late 2017 and all of 2018, the [AvgGrowth1] shows as 159774% because the site was down for maintenance for four months, Aug 2016 - Nov 2016. I need to find a way to create the [AvgGrowth] without being skewed by these outlier values.
I have experimented with different ways to avoid this problem. I tried using a Lookup in [AvgGrowth2], yet this didn't solve the problem because the site was down for so long. It it had only been down 1 month, this may have solved the problem.
I created a new field [FlagOutliers] and [Z Score] to identify not only when it is a very low value or Null, but to also say when the Z Score is >= 2 or <=-2.
I tried to research the CONTAINS function such as CONTAINS([FlagOutliers],<=1), but I don't know how to use this in the WINDOW_AVG function.
Any ideas? Please see the latest packaged workbook.
Site Traffic Example C.twbx 138.5 KB
I can't say that I fully understand the Average Growth part,
but I made an attempt to get some more reasonable numbers.
I made a field of Non-Flagged Traffic sums:
IF [FlagOutliers]=0 THEN SUM([Traffic]) END
Then I incorporated this into Traffic-YOYGrowth3:
(ZN([Traffic Non-Flagged]) - LOOKUP(ZN([Traffic Non-Flagged]), -1)) /
ABS(LOOKUP(ZN([Traffic Non-Flagged]), -1))
Then used that in AvgGrowth3:
IFNULL([Traffic-YOYGrowth 3],LOOKUP([Traffic-YOYGrowth 3])-1),
It's tricky for me to figure out how to validate the numbers,
but at least they look more reasonable.
Could you post an excel sheet with the expected results for this
Site and Billing Region?
Please see workbook v2018.1 attached in the Forum Thread.
276166table2.twbx 151.8 KB