I need to do a aggregation under IF statement in a calculated field

1. If a city is the same as selected by the user (parameter PAR_SELECT_CITY);

2. In case the condition 1 is true, then SUM(Number of records) - [PAR_SELECT_QTY]

   [PAR_SELECT_QTY] is a parameter that user choose to deduct from the total quantity

3. In case the condition 1 is false, then SUM(Number of records)

   IF [City] = [PAR_SELECT_CITY] THEN
       SUM([Number of Records])-[PAR_SELECT_QTY]
   ELSE
      SUM([Number of Records])
   END
   

However, IF Statament does not accept to mix aggregation and not aggregation

How do I solve this issue?

CodePudding user response：

As your error suggests, the issue is the mix of aggregate and "row level" data. In Tableau, you ideally want your row level data to be contained within an aggregate function.

i.e. sum(if true then 1 end) instead of if true then sum(1) end

For your example, you could try

SUM([Number of Records])
- 
AVG(IF [City] = [PAR_SELECT_CITY] THEN [PAR_SELECT_QTY] ELSE 0 END)

Your PAR_SELECT_CITY also needs to return an aggregate number. If PARA_SELECT_CITY = 5 (for example) and your dataset contains 100 rows, the AVG(PARA_SELECT_CITY) will also be 5, whereas SUM(PARA_SELECT_CITY) would return 500. Therefore the AVG should work as an aggregate function that returns the desired value.