1

I'm trying to create an API which will tell me the sum of metals awarded across the top 10 countries in my data of the olympics so I can create a visualization of it.

When creating the API i'm specifying SUM[medals] IN Countries which is a syntax error related to the IN operator, also not sure how I would have it cycle through only the top 10 countries on the list rather than them all. Any ideas?

enter image description here

AaronW2
  • 29
  • 5
  • Welcome to https://stackoverflow.com/. Please show what you have tried and add a brief description of your search efforts as is suggested in https://stackoverflow.com/questions/how-to-ask . You should provide a reproduceable example: https://stackoverflow.com/help/minimal-reproducible-example – Florian Metzger-Noel Apr 20 '22 at 09:44
  • Please provide enough code so others can better understand or reproduce the problem. – Community Apr 20 '22 at 09:44

1 Answers1

0

Assuming you have a set called Top_Countries defined however you like,

Then you can write a calculated field called, say, Top_Country_Medals defined as if [Top_Countries] then [medals] end

Alternatively, say you don't want to make a set, but you instead already have a string field called Top_Country_Name that has a value if the current country is in the top 10 and is null otherwise.

In that case, the calculated field would just be if not isnull([Top_Country_Name]) then [medals] end

Then your calculated field will evaluate to the # of medals for Countries that belong to the Top_Countries set (or alternatively, the countries that have a non-null value in the [Top_Country_Name] column), and to null for other Countries. Then you can place your new field Top_Country_Medals on any shelf as a measure and use whatever aggregation function you like - SUM, MIN, MAX, AVG etc

Note an if expression without an else clause evaluates to null if the if condition is not satisfied. And all aggregation functions silently ignore null values, leading to very simple calculation expressions if you take advantage of those facts.

Just naming a set in a calculated field, tests membership. You don’t need the IN keyword for that.

Alex Blakemore
  • 11,301
  • 2
  • 26
  • 49
  • It says "Expected type boolean, found string. Comparison in IF expression must be boolean type" teams is string and medals are in int for the amount won per country for gold, silver, bronze. – AaronW2 Apr 20 '22 at 00:54
  • Added a screenshot of what I mean – AaronW2 Apr 20 '22 at 01:33
  • How did you define the field Top_10_Teams ? If you create it as a set, then it will be a boolean valued expression and your calculation will be correct. -- Okay, from your duplicate question, it looks like you made a string field instead of a set and that field has a value only for the top 10 teams. That can work too. I'll update the answer to work for both – Alex Blakemore Apr 20 '22 at 21:22
  • @AaronW2 I hope the updated answer works for you. Welcome to Stack Overflow. For future reference, Its better to write one question - and then improve it as needed - than to write duplicate questions. Saves everyone time. – Alex Blakemore Apr 20 '22 at 21:33