7 Replies Latest reply on Sep 3, 2017 5:21 AM by Ben Topper

    ATTR and filtering

    Ben Topper

      Hi,

       

      Sorry about the lack of a workbook but I've not got access to Tableau today (other computer...)

       

      I have data which has 5 different products with sales volumes by year. I want to calculate the revenue so I made 5 parameter where I could put different per-unit prices in for each product, with a set price for the first 500 units of 1.5 each. Then I created a revenue formula which said:

       

      IF ATTR([Product] = "A" AND [Year] = 2016)

      THEN (SUM([Volume])-500) * [Parameter Product A Cost/Unit] + (500*1.5)

      END

       

      This worked fine, and then I did something similar for product B.

       

      However, if I then filter on the products, if I only filter on product A, the A revenue calc works, and if I only filter on product B the B revenue calc works, but if I filter on both, then neither works - they both come back as blank.

       

      I was using the ATTR() function to enable the (SUM([Volume]-500) calc but I think the ATTR is interacting with the filter somehow.

       

      Can anyone help (and explain what's going on?)

       

      Thanks,

        • 1. Re: ATTR and filtering
          Matt Lutton

          It is very difficult to help without a packaged workbook example -- you could mock up a scenario using sample data, or a subset of your own data, randomized prior to bringing it into Tableau.  But a .twbx is definitely the best way to get help here, as you likely are already aware.

           

          Without any knowledge of your data, I am just guessing but you might try:

           

          SUM(IF [Product] = "A" AND [Year] = 2016

          THEN ([Volume])-500) * ([Parameter Product A Cost/Unit] + (500*1.5)))

           

          The impact of this is that the SUM/aggregate is on the outside of the calculation, rather than inside the IF statement.  This should help avoid the dreaded "Cannot mix aggregate and non aggregate calc" error, but I do not know enough about your data and its granularity to know if the calculation will be accurate for your view.  This is where a .TWBX with some mockup of expected results would be helpful.

           

          Best wishes and good luck!

          • 2. Re: ATTR and filtering
            Jim Dehner

            Hi Ben

             

            If I understand your issue you have 2 calculated fields on the same dimension - to simplify the are like          

                 if dimension = 'a' then sales end

                  if dimension = 'a' then sales end

             

            then put a filter on A and a filter on B and it returns blanks

             

            the effect you are creating in the first formula for all records that contain A you place a value and all other records are Null

            Then you to just the opposite with second formula -

            when you do the combined filters all records will have null values form one of the formulas -

            To avoid that include an Else  0 end clause in each formula

             

             

             

            Jim

            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.

            • 3. Re: ATTR and filtering
              Ben Topper

              Sorry about the lack of a packaged workbook. I've just got to Tableau and quickly made one.

               

              The Excel data was this:

               

               

              The idea was to create a revenue calc based on some parameters (allowing me to change the price/unit). So my calc made (see "revenue" measure") was

               

              As such, for product A for 2016, it sums the volumes and * the product A price parameter, and for product B for 2016 it takes the volumes - 50 and multiplies by the product B price parameter (imagine the first 50 units are free or something). Then I can change the prices and see the results easily. I used the ATTR() function to enable the "sum[volume] - 50" calc to work.

               

               

              This all works fine when only product A or product B are filtered, but doesn't work when they are both in the visualization.

               

              Anyone know what I need to do?


              Thanks,

              • 4. Re: ATTR and filtering
                Jim Dehner

                Hi Ben

                see the attached

                There is a progression to show you haw this works -

                 

                This will calculate the volume based on prod only

                          if [Product]="A"  then [Volume] elseif [Product]="B"   then [Volume]  end

                This will include the date field 

                      if [Product]="A" and [Year]=2016  then [Volume] elseif [Product]="B" and [Year]=2016  then [Volume] end

                 

                And this includes the pricing parameters

                     if [Product]="A" and [Year]=2016      then [Volume] * [price a] elseif [Product]="B" and [Year]=2016  then [Volume] * [price b] end

                The produce this

                 

                filtered for A looks like this

                Filtered for B looks like this

                 

                 

                Is that what you wanted -

                 

                I think you got into trouble with the placement of your ATTR functions - I left all formulas at the dis aggregate level and let them be aggregated as I moved them to the viz

                your YEAR field is a number not a date and your product field is a string  in the viz I created I changed Year to discrete as I moved it to the db

                 

                Jim

                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.

                • 5. Re: ATTR and filtering
                  Ben Topper

                  Hi Jim,

                   

                  Thanks for the answer. I think the issue is that when you try to do something like:

                   

                  IF ATTR([Product] = "A" AND [Year] = 2016)

                  THEN (SUM([Volume])-500) * [Parameter Product A Cost/Unit] + (500*1.5)

                  END

                   

                  if you don't put in the ATTR() function, then the SUM([Volume])-500 doesn't work as the 500 is applied to each row, rather than the total (i.e with 2 rows of data, it will then subtract 1000 etc.). That's why I put the ATTR() function in - to get the 500 to be applied to the SUM, rather than to each row.

                  • 6. Re: ATTR and filtering
                    Jim Dehner

                    Hi Ben Yes I misplaced the ) around the equation - it should have been outside the sum

                     

                    but that wasn't the issue that prevented your filters from working properly

                    Jim

                    • 7. Re: ATTR and filtering
                      Ben Topper

                      Hi Jim,

                       

                      Sorry that's not what I meant. If you look at my file, you can see the formula includes a "volume - 50" calculation. This is to stop the price/unit being applied to the first 50 units. To get this to work, I needed to use the ATTR() function. However, the ATTR() function stops the filtering working properly, and this is my problem.

                       

                      Ben