I am trying to figure out why I am able to get a record count using a formula but not an aggregation rule.
I am trying to create a logical column called "Total Records" that is a count of all records. The logical column derives from a column that is the primary key for another table, in this case Enrolment ID.
I had originally selected Column Source Type > Derived From Physical Mappings and then selected the "Count" default aggregation rule, but this didn't work and gave me a count of zero.
I then selected "Derived from existing columns using an expression" and entered the expression below, which did work. How come the formula worked but not the default aggregation rule?
sum(case when
(
"Enrolment - College"."F College Enrolment"."Enrolment ID"
is not null
)
then 1 else 0 end)