Tableau: Aggregate and Non-aggregate error with Date calculations

Question

I'm having trouble with a calculation in tableau. I've created some calculations that are listed at the top that I use in the calculation I'm currently working on.

Last 30 days calc:

IF [Date] < (today() - 31) OR [Date] > today() THEN NULL ELSE [Date] END

Price-AvgWeighted calc:

SUM ([SaleTotal]) / SUM([Qty])

Calculation with ERROR:

IF NOT (ISNULL([Last30days])) AND [Rev]>500 AND QTY > 10 AND [Price] < (.7*([Price-AvgWeighted])) THEN 'True' ELSE 'False' END

ERROR: cannot mix aggregate and non-aggregate arguments with this function. (Highlights the "<")

My goal is to create a calculation that does the following: "If in the last 30 days revenue is greater than 500 and qty is greater than 10 for all previously sold and the current (available) price is less than .7 of the last 30 day weighted average sales price THEN True/false"

I'm also not sure how to incorporate the Status Dimension (shows type of product: sold/avil)

Any help would be greatly appreciated. Thanks

Answer 1

• It looks like you have to turn [Last 30 Days], [Rev], [QTY] and [Price] into aggregations since [Price-AvgWeighted] is an aggregation.

• Use the following:

IF NOT (ISNULL(ATTR([Last30days]))) AND SUM([Rev])>500 AND SUM([QTY]) > 10 AND SUM([Price]) < (.7*([Price-AvgWeighted])) THEN 'True' ELSE 'False' END

You can enroll in this Best Tableau Desktop Certification course online by Intellipaat to learn basic and advanced Tableau.