9 Replies Latest reply on Dec 12, 2016 10:52 AM by Joshua Milligan

    IF Expression: Cannot mix aggregate & non-aggregate

    Reuben Salisbury

      Hi,

       

      I'm currently getting the following error message when attempting to create the calculated field highlighted below:

       

      "Cannot mix aggregate and non-aggregate comparison or results in 'IF' expressions".

       

      My calculation is:

       

      COUNTD(IF [SL]>' ' OR [VL]>' ' THEN ATTR(GA.UserId) END)

       

      The data sources in play:

       

      1. CRM data including UserId, and subscription information held in SL & VL
      2. Google analytics, including UserId

       

      I am essentially try to create a calculated field that returns the active percentage of our total user base. Total users is coming from distinct on UserId from CRM, and active users is coming from distinct on UserId from Google analytics, where a subscription exists in the CRM SL or VL fields.

       

      Many thanks!

        • 1. Re: IF Expression: Cannot mix aggregate & non-aggregate
          Shinichiro Murakami

          Reuben,

           

          Difficult without seeing the data, but try this

           

          COUNTD(IF attr([SL])>' ' OR attr([VL])>' ' THEN ATTR(GA.UserId) END)

           

          "ALL" fields need to be aggregated.

           

          Thanks,

          Shin

          • 2. Re: IF Expression: Cannot mix aggregate & non-aggregate
            Joshua Milligan

            Reuben,

             

            I think you just need to change ATTR(GA.UserId) to GA.UserID.

             

            ATTR() is a special kind of aggregation in Tableau, so having [SL]>' ' OR [VL]>' ' which are row-level values and ATTR(GA.UserId) which is an aggregation together in the IF statement is what is givin gyou the error.

             

            Best Regards,

            Joshua

            • 3. Re: IF Expression: Cannot mix aggregate & non-aggregate
              Joshua Milligan

              Shinichiro,

               

              I don't think that will work because COUNTD can't operate on something that is already aggregated.  So, it should be

               

              COUNTD(IF [SL]>' ' OR [VL]>' ' THEN GA.UserId END)

               

              Though, I agree, without seeing the data there may be other issues that are impossible to see at this point.

               

              Best Regards,

              Joshua

              • 4. Re: IF Expression: Cannot mix aggregate & non-aggregate
                Reuben Salisbury

                Re: To Shin

                 

                Applying this suggestion results in a slightly different error:

                 

                "argument to COUNTD (an aggregate function) is already an aggregation, and cannot be further aggregated."

                 

                 

                Joshua Milligan

                 

                Applying this suggestion results in:

                 

                "all fields must be aggregate or constant when using the table calculation functions or fields from multiple data sources."

                 

                Any other pointers?!

                • 5. Re: IF Expression: Cannot mix aggregate & non-aggregate
                  Joshua Milligan

                  Ah, I understand the issue now and should have read more closely.  So, you do have to use an aggregation to get GA.UserID from a secondary source -- the problem is that the aggregation cannot be mixed with row-level fields in the same statement.

                   

                  How are you blending to get the UserID from GA?  That is, what field(s) in your primary source determine a unique UserID in GA? 

                   

                   

                  Those are the fields you'll  need to use in your IF statement.  For example, if you are blending based on fields X and Y, then your statement will be something like:

                   

                  COUNTD(IF [SL]>' ' OR [VL]>' ' THEN STR(X) + "|" + STR(Y) END)

                   

                  What that will do is give you the unique users based on the key (the fields that link to GA) rather than the value in GA.  You won't have to cast to strings if they already are and you won't have to concatenate if it's only one field, for example, it might be:

                   

                  COUNTD(IF [SL]>' ' OR [VL]>' ' THEN [X] END)

                   

                  Looks like you were right Shinichiro Murakami, it's hard to answer without seeing the data!

                   

                  Best Regards,

                  Joshua

                   

                  • 6. Re: IF Expression: Cannot mix aggregate & non-aggregate
                    Reuben Salisbury

                    Hi Joshua,

                     

                    I am blending on UserId. This exists in the CRM data and it's a custom dimension we have configured in GA.

                     

                    The primary source is the CRM and the secondary is GA. Using the below still returns the error "all fields must be aggregate or constant when using the table calculation functions or fields from multiple data sources."

                     

                    COUNTD(IF [SL]>' ' OR [VL]>' ' THEN GA.UserId END)

                     

                    I'm sure I'm missing something obvious here, sorry!

                    • 7. Re: IF Expression: Cannot mix aggregate & non-aggregate
                      Joshua Milligan

                      Reuben,

                      So if a UserId with SL and VL exists in the CRM data, is there always a corresponding UserId in GA?  Or, is it possible that the UserId does not exist in GA?

                       

                      If the former, then you should be able to use COUNTD(IF [SL]>' ' OR [VL]>' ' THEN CRM.UserId END) 

                      But, if the later, then you'll have to use some other technique to count the distinct number of users that exist in GA.  But that is likely going to involve some kind of table calculations that will depend greatly on the view you are trying to build.  I'd be happy to take a look if you have a packaged workbook available (with mocked up data as needed) with the view you are trying to build.

                       

                      Best Regards,

                      Joshua

                      • 8. Re: IF Expression: Cannot mix aggregate & non-aggregate
                        Reuben Salisbury

                        Hey Joshua,

                         

                        It's the latter! Probably explains why I'm running into trouble... So I've mocked up an example (see attached) which replicates the issue.

                         

                        I'm trying to answer:

                         

                        What % of subscribed users (i.e. have a value in SL or VL) are active (i.e. there's a record in GA). So far I've been attempting the calculated field in this question to give me the distinct count of "active" subscribed users, which I can then use to perform a calculation such as active_users/total_users*100 or similar. There might be an entirely different/easier approach.

                         

                        Many thanks!

                        Reuben.

                        • 9. Re: IF Expression: Cannot mix aggregate & non-aggregate
                          Joshua Milligan

                          Reuben,

                           

                          Here's one approach:

                           

                          First, filter the primary data source only to the set of users that are subscribed (have a value for either SL or VL).  Here, I've done it with a calculated field:

                           

                          Then, blend the two data sources together and use UserId from the primary in the view:

                          Now, we've got a list of all subscribed users and all active users.

                           

                          Next, we'll use some table calculations to add up the totals of each along with dividing to get the percentage:

                           

                           

                           

                          The WINDOW_SUM function will add up within a window (defined by how you compute the table calc).  Here, we'll treat the entire table as the window such that Count of Subscribed adds up all the rows and Count of Active adds up all the cases where there is a GA.UserId.

                           

                           

                          You'll notice we get the same result for each row in the table, because the window for each row is the same (entire table):

                           

                           

                           

                          Now, we just have to clean up a bit to get the display we want.  Assuming you just want to show the value, I'd use a FIrst() == 0 calculation to filter to the first row in the table above and then remove GA.UserId from the view (it's not needed).  CRM.UserId does have to be there to define the view level of detail, but we can just hide row headers (use the drop down on the field on Rows).  Also, we can remove the Count calculations as they were just there to help us work it out.

                           

                          Your final view could look something like this:

                           

                           

                          Now, the catch might be if you have a huge volume of data (lots of UserIDs) as the performance might start to degrade (because even though you don't see it in the final table, all those UserIDs are pulled into the cache and Tableau would be doing lots of calculations in memory).  If you run into that or have additional complexity to the problem, you might have to consider using outside processes to blend the data outside of Tableau prior to visualizing it -- but hopefully this approach works.

                           

                           

                          Hope that helps!

                          Joshua