Hi Guys, I’m trying to take distinct count of client names (and show it as a KPI) if start date on client level falls in current month. Getting error for mismatched aggregation, Please help.
distinct_countIf(
{client_name},
dateDiff(now(),min({start_date},[{client_name}]),“MM”)=0
and dateDiff(now(),min({start_date},[{client_name}]),“YYYY”)=0
)
Hello @spitfire, welcome to the QuickSight community.
I am not certain this will fix all aggregation errors, it may also be caused by the min aggregation with the partition inside of the dateDiff, but my first suggestion would be moving this to an ifelse statement rather than distinct_countIf.
Return {client_name} if the statement is true, otherwise return NULL. Then you can aggregate the returned value by distinct_count. Let me know if that fixes the error, or if you receive a different error on the calculation.
Hey thanks for the reply, I changed it to below:
distinct_count(
ifelse(
dateDiff(now(),min({start_date},[{client_name}]),“MM”)=0
and dateDiff(now(),min({start_date},[{client_name}]),“YYYY”)=0,{client_name},0
)
)
now the error changed to “The syntax of the calculated field expression is incorrect. Correct the syntax and choose Create again.”
