3 of 3 people found this helpful
You need a calc field something like this:
Andy, is there any functional difference in using if then to iif?
AVG(IF [Category]='New' THEN [Units] END)
Will produce different results, because the default ELSE return is NULL when left out, and the IIF must have return values for the arguments True and False . Also the IIF function has an optional return argument for Unknown, and that can be handy in some cases.
You can also check your log files and inspect he SQL Tableau is sending to the data source.
I would like to sum a list of account names if they meet a certain criteria. If possible, I would like to use the sum(iff. For example, instead of having just new in the above example can I have something like: sum(iif([category]='administration employees','other',[amount],null))
Saw your post and wondering whether you can solve my question -
I am trying to find out # of markets that were profit with last 3 months, 6 months or 1 yr, using monthly revenue and expense data to calculate it. I built a calculated field -
Countd(if sum([monthly_profit])>0 then [sales_market])
And errors pops up, saying that I cannot use aggregation function within an aggregation function. What should I then?
I had a similar problem, wanted to count the total number of incidents of a variable if the variable was greater than 100. Here is the formula that finally worked - I created a new variable:
COUNT( IF (VARIABLE >100) THEN VARIABLE ELSE NULL END)
I also found I could not mix integers and strings, the variable needed to be the same. I don't know if that is true across the board, but is was in my case. I checked the data to assure the variable was processing correctly.