5 Replies Latest reply on Aug 2, 2018 7:27 PM by swaroop.gantela

# Combinations of difficult calculations

Using version 18.1

In sheet TableCalcs2 I have created a series of Table calculations and the SUM([Traffic]) for a number of Sites (XXX) for [Traffic] that is accumulated monthly since 2012.

I need to create an Alert when:

A) Average Growth [AvgGrowth], which is a Table Calc, is greater than 20% in the most recent month of data

AND

B) when the SUM([Traffic]) is not equal to 0 in any of the prior 12 months.

A) I tried to start with the first step - what is the most recent month, and have created this through a combination of Calculated Fields: LastMonth, ThisYear, and LastMonthDate. That's as far as I can get.

How do I ask it about [AvgGrowth] when [New Date] = to LastMonthDate to return True when it is greater than 20%?

B) The AvgGrowth is not critical when it is above 20% when some of the months [Traffic] were equal to 0 (or null), because that means the Site was out-of-service for a period of time, thus the monthly AvgGrowth is thrown off by the Site's recovery. This happens with Site = AYQ because there was no Traffic during the months of December 17 and February 18.

I created a Site Traffic Example file attached for your use.

• ###### 1. Re: Combinations of difficult calculations

Hi Heidi,

BR,

bharat

• ###### 2. Re: Combinations of difficult calculations

These sites do not do anything as complex as the formulas I need. I still need help!

Heidi

• ###### 3. Re: Combinations of difficult calculations

Heidi,

I did not fully catch the gist of the AvgGrowth or YOY,

Flag Most Recent Month:

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:

IF ATTR([MostRecentMonth])
AND [AvgGrowth]>.2
AND [AnyZerosPrior12]=0
ELSE "Standard"
END

workbook v18.1 attached in the Forum Thread.

• ###### 4. Re: Combinations of difficult calculations

Swaroop,

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.

• ###### 5. Re: Combinations of difficult calculations

Heidi,

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:

WINDOW_AVG(

IFNULL([Traffic-YOYGrowth 3],LOOKUP([Traffic-YOYGrowth 3])-1),

-12,0)

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?