I have this table:
Genres Episodes
Drama 2
Drama 1
Drama 0
Comedy 2
Comedy 1
Comedy 0
Kids 3
Kids 2
Kids 1
Kids 0
I want to create a calculated field:
Genres Episodes Criteria
Drama 2 Complete
Drama 1 Partial
Drama 0 Obsolete
Comedy 2 Complete
Comedy 1 Partial
Comedy 0 Obsolete
Kids 3 Complete
Kids 2 Partial
Kids 1 Partial
Kids 0 Obsolete
My calculated field formula throws an error:
ifelse({Genres} = 'Drama' and (count({Episodes}) = 2), 'Complete',
{Genres} = 'Drama' and (count({Episodes}) = 0), 'Obsolete',
{Genres} = 'Drama' and (count({Episodes}) = 1), 'Partial',
{Genres} = 'Comedy' and (count({Episodes}) = 2), 'Complete',
{Genres} = 'Comedy' and (count({Episodes}) = 0), 'Obsolete',
{Genres} = 'Comedy' and (count({Episodes}) = 1), 'Partial',
{Genres} = 'Kids' and (count({Episodes}) = 3), 'Complete',
{Genres} = 'Kids' and (count({Episodes}) = 0), 'Obsolete','Partial')
Error:
Mismatched Aggregation. Custom Aggregations cannot contain both aggregate Count and non-Aggregated fields.
Can someone help me with this!
If anyone runs into this issue of mismatched aggregation, I found a workaround.
Simply convert the string into a numerical format (formula) as I did:
genres_numbers:
ifelse({Genres} = 'Drama',1, {Genres} = 'Comedy',2, 3)
Secondly, create a count formula for the Episodes column:
count_episodes:
count({Episodes})
Afterwards, take the average of the both and assign the Criteria values as:
ifelse(avg({genres_numbers}) = 1 and avg({count_episodes}) = 2, 'Complete'...)
This worked for me :)