distinctCountOver() throwing SQL exceptions with any ifelse() in First Argument

Q&A
, ,
1

Hello,

I’m trying to get a distinct count of PRE_FILTER values based on a condition.

Even when I simplify the calc to the most basic of levels, I cannot get distinctCountOver() to work with any ifelse() condition in the first argument.

I’ve even tried the following and still get a SQL exception when it’s added to a table grouped by Field1:
distinctCountOver(ifelse(1=1,1,1), [{Field1}], PRE_FILTER)

Field1 is a simple string value in the dataset itself.

Doing countOver(ifelse(1=1,1,1), [{Field1}], PRE_FILTER) does not cause any errors. But for my use case I need a distinctCount, and I need the ifelse statement.

2

Hey @dsost

This calculation saved while I was testing this issue:

image
image393×242 5.89 KB

What does your dataset look like and is Field1 also a calculated field?

3

Hey @duncan,

The calculation saves on my end too, it only throws errors when the calculation is added to a table visualization in the analysis.

Field1 is not a calculated field, it’s a string column in the dataset itself.

4

Hi @dsost,

It’s been awhile since we last heard from you on this thread; are you still encountering the same issue or were you able to find a work around in the interim?

Is your visual presenting you with an error that’s something like ‘visual calc reference missing? If that’s the case; this error is normally caused by not including a specific field in your visual that’s being utilized in the calculated fields. Make sure that all fields used in the calculation are also added to your table, if you don’t want/need those columns, you can ‘hide’ them from the visual.

Let me know if that works for your case or if you have any additional questions. If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

5

@Brett I found a work around by essentially moving all the filters that were in the visual into calculated fields with parameters instead, omitting the need for a PRE_FILTER LAC and was able to use distinct_count instead (which was really annoying but it worked).

However, I wouldn’t call the issue “fixed” since still occurs when I use any ifelse in distinctCountOver.

Here’s an example when I test the same calc using countOver vs distinctCountOver.

trial_sf_id is a UUID text field directly from the database with no nulls.

Both calculated fields save successfully.

image
image729×233 8.65 KB

image
image608×237 9.46 KB

Both calc fields save successfully.

But when I add them to visuals (any type, kpi, table, it doesn’t matter), distinctCountOver always throws a SQL exception error (not a ‘visual calc reference missing’ error).

image
image1332×782 82.3 KB

image
image1453×846 106 KB

This seems like a bug with distinctCountOver, but I’m not sure how to report it as such.

FWIW I’m not the only one encountering this issue, there’s another topic here along the same lines.

6

Hi @dsost,

Interesting, agreed that this may be a bug. You can create a support ticket to report this through the following link:

7

Hi @dsost,

Following up here as it’s been awhile since last communication took place on this thread; were you able to create a support ticket and if so, were they able to assist further with this potential bug?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

8

Hey Brett thanks for following up – I’m currently working with my company’s SRE to submit the ticket. You can close the topic :slight_smile:

2 Likes