SumIf calculation and grouping by dimensions

[buldozek]

Hello,

here is the sample data:

Shipping method	Year	Country	Quantity
Plane	2022	Albania	33
Plane	2020	Bulgaria	45
Train	2022	Greece	34
Train	2021	Greece	20
Car	2021	Germany	3

I would like to calculate the following: sum of quantity IF the shipping method is Plane, minus sum of quantity IF the  shipping method is Train. Then I'd like to group the results per Year, Country etc. so I would like to get these results by specific dimension, for Year 2022, 2021 etc

How to achieve something like this?

[Luke_C]

Hi @buldozek 

No countries have different shipping methods, so wouldn't the numbers just be the same as what you have? What does your expected output look like

[buldozek]

Sumif(shipping method = 'Plane' - 78) - Sumif*shipping method = 'Train' - 54) = the result is 24 (for the whole dataset). Now I'd like to distribute this number among each Year and Country. So I'd like to do the same calculation but within specific Year.Hope this clarifies :)

[messi007]

@buldozek,

Please see below :

Hope this solve the problem if not please share with us what you expect to have :)

Attached the workflow,

Regards,

[JeroenJacobs]

With a formula tool, select the Quantity field and make the following calculation;

IF [Shipping method]='Plane' THEN [Quantity] ELSEIF [Shipping method]='Train' THEN -[Quantity] ELSE 0 ENDIF

Next to that, make a summarize tool, group by Country and Year, and Sum Quantity.

Done :-)

[buldozek]

Hi, @messi007 

this doesn't solve the problem. Let's say I want to get (3) subsets of my data filtering only 2020, 2021, 2022. Then I'd like to do this calculation. So for 2022 the result would be 33-34, for 2020 45 - 0 (there isn't any Train in shipping method for 2020) etc