7 Replies Latest reply on Oct 17, 2018 11:45 AM by David Baker

    LOD Question

    David Baker


      I am trying to create a custom impression field which is proving to be a bit more challenging than expected. I get an aggregation error thrown at me about mixing aggregate & non aggregate data. Can someone provide insight into how to fix this? What I'm trying to do in the second half of the post is replace the reach (aka impression) value for posts that were shared with an average reach for all posts without shares that are not from Instagram (aka include Twitter, Facebook, LinkedIn, etc).


      SUM(IF [Account Type] = “Instagram”

      THEN 0.3*[Followers At Post Time (SUM)]

      ELSEIF [Shared?] = “Yes” AND [Account Type] != “Instagram” AND [Post Shares (SUM)] = 0

      THEN AVG([Post Reach (SUM)])





      Thanks for the help!

        • 1. Re: LOD Question
          Joe Oppelt

          For one thing, you can't have an aggregate within an aggregate.

          You have an AVG inside a SUM.


          TO aggregate aggregates, use WINDOW_SUM instead of SUM in that calc.  But everything inside a table calc will need to be aggregated, so inside the WINDOW_SUM parens, do ATTR([Account type]) instead of just [account type], for example.  All the fields inside the table calc need this.


          You might be able to do that AVG in a FIXED LOD, which would then let you include it in the SUM() logic instead.


          A sample workbook here would really help me show you some of this.

          1 of 1 people found this helpful
          • 2. Re: LOD Question
            Jim Dehner

            Hi David

            you have an aggregation in there somewhere - my guess is  [Post Shares (SUM)]


            when that happens the other dimensions and measures need to be aggregated like account type needs to be attr(account type)


            but when you get all that fixed there is another issue  - the statement below will always be FALSE - because if account type = instagram  = true will trigger the first clause

                      ELSEIF [Shared?] = “Yes” AND [Account Type] != “Instagram” AND [Post Shares (SUM)] = 0


            if you wan this to work make the second clause the first clause - then move the first clause to the else if statment



            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.



            1 of 1 people found this helpful
            • 3. Re: LOD Question
              David Baker

              Hi @JoeOppelt and @JimDehner, thanks for your help! I feel like I'm close, just struggling with LOD's (I know a fair amount Tableau but am still learning LOD's and the power of calculated fields). I've attached a sample workbook here.


              Here's some context for what I am trying to achieve. The social media publishing tool that we use aggregates impressions (aka reach) that come from sharing a post beyond our original audience. This skews another calculation that we use (Engagement Rate = Total Engagements/Impressions) which makes our content look like it performed poorly if it was shared because the impression number becomes larger than it's supposed to. Because of this, we are trying to make a calculated field that looks at whether a post has more than 0 shares and if it does, take the average reach for posts that have no shares in order to normalize impressions. Instagram does not provide impressions anymore so, we are taking 30% of followers at time of post to derive an estimate.

              • 4. Re: LOD Question
                Joe Oppelt

                This compiles cleanly:


                IF attr([Social Network]) = "Instagram"
                THEN 0.3 * attr([Followers At Post Time (SUM)])
                ELSEIF ATTR([Shared?]) = "Yes"
                   if attr([Post Shares (SUM)]) = 0 then AVG([Post Reach (SUM)]) END



                But I'm not sure that really what you want to do.


                What are you trying to accomplish here?

                • 5. Re: LOD Question
                  Joe Oppelt

                  That calc is doing this:


                  If network is instagram, do .3 times the value of [Followers...]


                  The ELSEIF  is handling all non-instagram stuff, so no extra check on that is needed.  (Tableau evaluates from top down.  And when it hits a TRUE condition, it stops evaluating.  So all Instagram stuff is captured at the top.)


                  So when we're down in the non-instagram part, there is another check.  If SHARED then see if Post Shares = 0.  If so, then grab the value of post-reach.


                  Every other condition evaluates to NULL.


                  All the ATTR() stuff is necessary because of the AVG() aggregation.


                  Do you want the calc evaluated for every ROW of data?  Or do you want it evaluated at some dimensional rollup?  If you want it done for each row, then you can take off all the ATTR and AVG stuff.  Then it becomes another measure value computed at the row level rather than at the aggregate level.

                  • 6. Re: LOD Question
                    Joe Oppelt

                    And if you want it done at the aggregate level for the dimensions on the sheet, then


                    attr([Post Shares (SUM)]) = 0


                    needs to be


                    sum([Post Shares (SUM)]) = 0

                    • 7. Re: LOD Question
                      David Baker

                      This is exactly what I need! THANK YOU!!!