Count/Sum KPI based on Year and Category

[haroldofzakarum]

Hi!

I'm having trouble writing KPI expressions for counting and summing values based the following example columns:

fiscal_year	amount	category
2021	111	abc
2022	111	abc
2022	111	def
2023	111	ghi
2024	111	jkl
...	...	...

I want to sum the amount where fiscal_years are between 2024 and 2028 where category is only x.

I also want to count total distinct categories where fiscal_years are between 2024 and 2028 where category is only x.

I can count and sum the values and show the KPIs, but the values are off since I haven't found the right expression.

I know it's possible, but I can't get unstuck. Thanks for any advice!

[BrunPierre]

Hi, I recommend you read about Set analysis and set expressions.

Anyways, as below.

Sum({$<fiscal_year={">=2024 <=2028 "},category={'x'}>} amount)

Count({$<fiscal_year={">=2024 <=2028 "},category={'x'}>} DISTINCT category)