6 Replies Latest reply on Jul 24, 2018 12:29 AM by Mavis Liu

    use IF in aggregated expression

    jon rios

      i have a calculation that i want to return the value if certain criteria is met, but i keep getting errors.. how can i manipulate the expression to work

       

      IF [ label ] = "12 to 24" or [ label ] = "24 to 36" then [Avg Monthly Purchase Volume] END  - error: "cannot mix aggr & non-aggr results in IF statement"

       

      IF [ label ] = "12 to 24" or [ label ] = "24 to 36" then Sum([Avg Monthly Purchase Volume])  END  - error: "Aggr to SUM is already an aggregate"

       

      sum(IF [ label ] = "12 to < 24" or [ label ] = "24 to < 36"  then [Avg Monthly Purchase Volume]  END ) - error: "cannot mix aggr & non-aggr results in IF statement"

       

       

      i just want to return the value if the condition is met!

        • 1. Re: use IF in aggregated expression
          Deepak Rai

          IF ATTR( [ label ]) = "12 to 24" orATTR( [ label ] )= "24 to 36" then [Avg Monthly Purchase Volume] END

           

          IF ATTR([ label ]) = "12 to 24" or ATTR([ label ] )= "24 to 36" then WINDOW_SUM(([Avg Monthly Purchase Volume]))  END

           

          WINDOW_sum(IF ATTR([ label ]) = "12 to < 24" or ATTR([ label ]) = "24 to < 36"  then [Avg Monthly Purchase Volume]  END )

           

           

          If It Does N't Help, Then We need a workbook to help you

          Thanks

          Deepak

          • 2. Re: use IF in aggregated expression
            jon rios

            i hate just getting an answer here on the forum, i want to understand WHY...

             

            can you explain, when one should use an Aggregate in a calculation formula, and when it should not be used??  if i know this, i can avoid the same mistake in the future

            • 3. Re: use IF in aggregated expression
              Robertino Bonora

              Hi, Tableau only allows compare measures in an LOD when this are all aggregates or none is.

               

              Aggregations are functions like SUM, AVG, MIN, MAX, etc.

               

              So, in your case, you were trying to analyze a non aggregated dim with the condition [ label ] = "12 to 24" with the aggregated result measure like [Avg Monthly Purchase Volume]. Thats why Deepak told you to use ATTR(), this is a function to compare and analize and individual value of a dim and in addition, this function turns into aggregated the dim [ label ].

               

              Answering to your question, if you for example showing sales in a sheet, you can thrown the measure [Sales] to the rows and tableau automatically shows you SUM([Sales]) because tableau needs to aggregate the data to show it to different levels.

              In the other case, if you create a calculated field like Calculation1: SUM([Sales]) and you thrown it to the rows tableau its gonna recognize it like AGG([Calculation1]) because this measure is already and aggregation.

               

              Robertino

              • 4. Re: use IF in aggregated expression
                jon rios

                but the Label is a Dimension, only measures can be aggregated (that is how I understand it)...  why is tableau forcing me to apply an aggregation on a Dimension? 

                 

                the original aggregation is on the entire table, now I am just saying, "Filter on Dimension - label -for "12 to 24" and 24-36" give me the output.  tableau is saying "NO!  we will not provide a value for a sub-set of the table only on the entire table!"

                 

                if i redo my table on SQL server and filter the table on my Dimension and re-connect to Tableau, will it provide the calculated output? yes. So why not within Tableau.  It is just hard to understand the logic.  if i am wrong and your interested please correct me

                • 5. Re: use IF in aggregated expression
                  Robertino Bonora

                  Thats why Tableau understand that label is a dimension and not a measure, so if you has to use two or more measures or dimensions in one calculated fields, all has to be aggregated or non aggregated at the same time.

                  In your case, the measure [Avg Monthly Purchase Volume] is aggregated, so the other/s dim/measure has to be too.

                   

                  To turn [label] to aggregated you has to specif say to Tableau to convert it with ATTR().

                   

                  Robertino

                  1 of 1 people found this helpful
                  • 6. Re: use IF in aggregated expression
                    Mavis Liu

                    Hi Jon,

                     

                    You could always do the aggregation afterwards:

                     

                    AVG(IF [ label ] = "12 to 24" or [ label ] = "24 to 36" then [Monthly Purchase Volume] END)

                     

                    So please use your disaggregated field (in this case I have just used Monthly Purchase Volume) then put the average as the aggregation AFTER the IF statement has been processed.

                     

                    Using the alternative option as Deepak has shown you, exactly what the others have said, everything in the calculation must be on the same level of aggregation, including dimensions. You can use options like min/max/attr to aggregate your dimensions.

                     

                    Thanks,

                     

                    Mavis