Best Of

@Juliane You will want to do these in Analyzer if you're planning on doing any dynamic filtering or date filtering. In ETL, you won't be able to do aggregations and still maintain your raw data. In those cases, you will want to do aggregations in Analyzer.

When you want to sum things that meet certain conditions, you will want to use a CASE WHEN statement, which is like IF...THEN... :

SUM(case when 'Field' = 'Condition' then 1 else 0 end) - This would give you a count of every row that meets the condition.

SUM(case when 'Field' = 'Condition' then 'Field' else 0 end) - If your field is a number, then this would give you the sum of every row that meets this condition.

If you have multiple conditions for a field, you can use IN instead of equals:

'Field' IN(1,2,3) would look at every row that has 1,2, or 3.

So for your example of a sum of 1,2,3:

SUM(case when 'Field' IN(1,2,3) then 'Field' else 0 end) - Not sure if you're going for a SUM or COUNT but you can use examples above to modify as needed.

If you want to look at what doesn't meet a condition, then you could use the following:

SUM(case when 'Field' <> 'Condition' then 1 else 0 end) - Or for multiple conditions it would 'Field' NOT IN(1,2,3)

So in your sum columns and percent columns, you would just replace what's in your SUM statements with the case when statements that meet your conditions.

@JackB116 you are on the right track. When you want to look at multiple conditions, you want to use 'and'. So you would just replace 'then when' with 'and'

CASE when `Invoiced Month` in ('10', '11', '12') 

and 'Invoiced Year' = '2022'

and `Sales Order Type` in ('PRE ORDER', 'PRE ORDER POST DEADLINE') then `Row Net` else 0 end