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

# ATTR and filtering

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

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

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

Sorry about the lack of a packaged workbook. I've just got to Tableau and quickly made one. 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

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

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

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

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