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

    Combinations of difficult calculations

    Heidi Kiser

      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.

       

      Thanks in advance!!  Heidi

        • 1. Re: Combinations of difficult calculations
          kumar bharat

          Hi Heidi,

          Please check these workbooks related to site traffics,it may help you out.

          Tableau Public

          Tableau Public

          Tableau Public

          Tableau Public

          BR,

          bharat

          • 2. Re: Combinations of difficult calculations
            Heidi Kiser

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

             

            Heidi

            • 3. Re: Combinations of difficult calculations
              swaroop.gantela

              Heidi,

               

              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:

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

               

              Please see sheet Combined Alert in the

              workbook v18.1 attached in the Forum Thread.

              • 4. Re: Combinations of difficult calculations
                Heidi Kiser

                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
                  swaroop.gantela

                  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?

                   

                  Please see workbook v2018.1 attached in the Forum Thread.

                   

                  276166table.png