SumIf calculation and grouping by dimensions

Question

Hello,

here is the sample data:

Shipping methodYearCountryQuantityPlane2022Albania33Plane2020Bulgaria45Train2022Greece34Train2021Greece20Car2021Germany3

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?

buldozek · Answer

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

JeroenJacobs · Answer

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 :-)

messi007 · Answer

@buldozek,

Please see below :

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

Attached the workflow,

Regards,

SumIF.yxmd