Distinctcountover partition by field syntax error

Question & Answer
, , ,
1

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.

2

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
3

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
4

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?

5

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

6

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

7

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

image

image

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

8

Thanks David - I was able to get the calculation working