Distinctcountover partition by field syntax error

This is the logic for the calculated field:

distinctCountOver({TEAM_NAME}, [{X-Axis Grouping}, Ratio], PRE_AGG) / distinctCountOver({RESOURCE_NAME}, [{X-Axis Grouping}, Ratio], PRE_AGG)

When I create the calculated field I am getting no errors, but when I try to display the calculated field in a visual I am getting a syntax error.

I found a similar question and answer here (Distinctcountover not taking calculative field as partition by field - #2 by Jesse), although the solution presented does not apply to my use case, as the field I am trying to partition by (âRatioâ) is a calculated field based on a parameter in the analysis.

Hi @guhifdgh - I believe the distinctCountOver provides multiple values depend upon the group by and this is why the error is coming when you are doing the division. Can you please provide the sample input and expected output and check your problem statement.

By saying that letâs hear from other experts @Biswajit_1993 @duncan @David_Wong

Regards - Sanjeeb

1 Like

Hi @guhifdgh,

Can you show how youâre calculating Ratio and the error message youâre getting?

Is it possible for distinctCountOver({RESOURCE_NAME}, [{X-Axis Grouping}, Ratio], PRE_AGG) to be 0?

1 Like

Thanks for the reply @David_Wong - the calculated field âRatioâ is calculated as follows:

âifelse(\${RatioDesired} = âJob Level Distributionâ, {JOB_LEVEL}, \${RatioDesired} = 'âJob Familyâ, {JOB_FAMILY},\${RatioDesired} = âJob Family Groupâ, {JOB_FAMILY_GROUP}, \${RatioDesired} = âAssociate vs Non-Associateâ, {ASSOCIATE_TYPE},
\${RatioDesired} = âProduct/Tech/Designâ, {Product/Tech/Design}, {ROLE_NAME})â where âRatioDesiredâ is a parameter that the user selects from a dropdown within the dashboard.

It is possible for distinctCountOver({RESOURCE_NAME}, [{X-Axis Grouping}, Ratio], PRE_AGG) to be 0, could that be what the problem is related to?

What happens if you try to add the numerator and denominator separately to a visual? Does QuickSight give you the same error?

If I separate out the numerator and denominator it gives me the same error in both cases.

âYour calculated field expression contains invalid syntax. Correct the syntax and try againâ Show details â source error code: DISTINCTCOUNTOVER_INVALID_ARGUMENT

I tried to create similar calculated fields in my analysis and didnât get any errors.

Is {X-Axis Grouping} a calculated field? If so, can you show the calculation?

Thanks David - I was able to get the calculation working