OVER function in Calculated Expression

[Marcio Souza]

Hi There!

I'm learning about the use of OVER Function, and I can't figure out how to use it on Calculated Expression. I got a database that contains all soccer match from Europe during the last years, and I'm trying to count how many matches each league has each year.

I have created a calculated column using OVER, as shown below:

Count([match_api_id]) OVER ([season_id],[league_id])The above aggregation returns the matches total for each league given the season as I would expect from OVER Function. I'm struggling to understand why the same aggregation would not work when I try to use it on the below code inside a Calculated Value:

FIRST(

CASE WHEN [name] "${markedLiga}" THEN COUNT([match_api_id]) OVER (All([country_id]))

ELSE COUNT([match_api_id]) OVER ([season_id],[league_id])

END

)I know that I can use the calculated column to show the number of matches for each season using First(). Still, my intention is to learn how to use Calculated Expressions in a dynamic item such as Calculated Value in the text area. I'm getting an error message stating that the Expression is not valid.

Regards,

Marcio

[Manoj Singh]

Can you quickly attached the sample analysis file with embedded data so that it would be easy to change the expression accordingly

[Marcio Souza]

Sure!

 

Take a look at the attached DXP file,

 

Regards,

 

Marcio

[Marcio Souza]

Sure!

Take a look at the attached DXP file,

Regards,

Marcio

[Manoj Singh]

I have reviewed the attached analysis file and you can use the below expression in it

 

==========================

 

FIRST( CASE WHEN [name] <> "${markedLiga}" THEN COUNT([match_api_id]) OVER (All([country_id])) ELSE COUNT([match_api_id]) OVER Intersect([season_id],[league_id]) END )

 

============================

[Marcio Souza]

Thank you, Manoj Singh!

I need to learn about how the Intersect function works.

Regards

Marcio