Ifelse, Distinct Count for Month-to-date Performance

Question & Answer
, ,
1

I’m trying to create a calculated field to get MTD unique count of new “issuers”. I’m unable to proceed due to syntax error. can anyone help out?

Formula: ifelse(dateDiff({First Disbursed Date}, now(), “MM”) = 0 AND {First Disbursed Date} <= now() AND {transaction_status} = ‘new’, distinct_count({Issuer Number}), 0)

Thanks!

2

Hi @ntjc ,

Assuming that you’re getting an aggregation error, e.g.

Screenshot 2024-01-17 at 5.59.57 AM
Screenshot 2024-01-17 at 5.59.57 AM1434×386 37.7 KB

it’s because of the aggregation in the ifelse function (here)

Use the distinct_countif() function instead.

Hope that helps.

ws

3

@ntjc you can’t have aggregated and non-aggregated values in a custom aggregation.

ifelse(
        dateDiff({First Disbursed Date}, now(), "MM") = 0 AND {First Disbursed Date} <= now() 
        AND {transaction_status} = 'new', distinct_count({Issuer Number}), 
        0
    )

When you hover over the errored formula, it will show the actual error message.

image
image3732×924 140 KB

you can try distinct_countif() instead
something like this

distinct_countif(
    {Issuer Number}, 
    dateDiff({First Disbursed Date}, now(), "MM") = 0 
 AND {First Disbursed Date} <= now() 
 AND {transaction_status} = 'new'
)