1 Reply Latest reply on May 10, 2013 6:43 AM by Jonathan Drummey

    aggregate

    Lou Papay

      trying to understand aggregate errors in 'if' statements

       

      Trying to compute Variance of ASP from 'this year vs. last year.

       

      I have ASP as a calculated aggregate field

      sum() / sum()

       

      then I'm using 'DatePart to find last year and want to use ASP, but I get the dreaded aggregate non-aggregate error with my If statement

       

      if DATEPART('year',[Date])= (DATEPART('year',Today())-1) then else 0 end

       

      I need to use this process for other measure comparisons

       

      Not sure how to use the ATTR function or if it will help?

       

      Any solutions or does need corrected in SQL?

       

      Thanks so much.

      Lou

        • 1. Re: aggregate
          Jonathan Drummey

          Hi Lou,

           

          I think there's a much easier way to get to the calculation you want, which is to use table calculations, You can right-click on the ASP pill, choose Quick Table Calculation->Year over Year Growth and it will give you the variance, which is -100% in a lot of cases because the many customers have no data for 2013 in the database.

           

          In general, I really like ATTR() because it's a handy error checking, you'd be wrapping that around the [Date] fields in the above. However, for the Tab Customer Yr vs Yr view, if you want to show both years in the view then ATTR() wouldn't help and you'd need table calcs. The reason is that the dimensions in the view (the Date and the Customer Name) are used as GROUP BY's in the query that Tableau issues to the data source while your regular aggregate measures are (in general) computed in the SELECT statement. So an aggregate can only "see" the results within it's particular intersection of dimensions, i.e. that row in the query result set. Tableau's solution to look across the rows of data returned in the result set is table calculations, which can be set up to compute across, down, or most any way you want over the data. In this case, the default Compute Using of Table (Across) sets up the addressing on Year and partition on Customer, which is what you'd want.

           

          Jonathan

           

          PS: This particular chunk of the forums is for meta-discussion about the forums, you'd get much quicker response by posting in the regular forums.