How do I calculate % of distinct count out of total distinct count AND graph it?

Q&A
, , , , , , ,
1

Hello,

I currently have a custom categorical field called “Users in Conversation Levels”, defined as the following:

ifelse(
  distinctCountOver({convo_id}, [{user_id}], PRE_AGG) = 1, "1 Conversation",
  distinctCountOver({convo_id}, [{user_id}], PRE_AGG) = 2, "2 Conversations",
  distinctCountOver({convo_id}, [{user_id}], PRE_AGG) = 3, "3 Conversations",
  distinctCountOver({convo_id}, [{user_id}], PRE_AGG) = 4, "4 Conversations",
  distinctCountOver({convo_id}, [{user_id}], PRE_AGG) >= 5, "5+ Conversations",
  NULL
)

I want to calculate, for each level, the % of users out of total users. This is my current query:
distinctCountOver({user_id}, [{Users in Conversation Levels}], PRE_AGG)/distinctCountOver({user_id}, [], PRE_AGG)

However, when I try to plot a vertical bar chart with Users in Conversation Levels as the x-axis, I get the error “Your calculated field expression contains invalid syntax. Correct the syntax and try again.” with the error code “DISTINCTCOUNTOVER_INVALID_ARGUMENT” even though I was allowed to create that calculated field. What is going on?

Thanks so much in advance.

2

Hi @zijingxu,

This is an issue around multiple window functions as Quick Sight does not allow window functions to be nested within another.

In terms of a work around; what if we simplified it a bit for your visual’s sake and started with just running a distinctcount calc:

Conversation Count
distinctCountOver({convo_id}, [{user_id}], PRE_AGG)

And then used that in an ifelse:

Convo Category
ifelse(
{Conversation Count} = 1, ‘1 Conversation’,
{Conversation Count} = 2, ‘2 Conversations’,
{Conversation Count} = 3, ‘3 Conversations’,
{Conversation Count} = 4, ‘4 Conversations’,
’5+ Conversations’)

I tested out on a sample analysis in Arena (and added an extra breakdown of region for testing), feel free to take a look: How do I calculate % of distinct count out of total distinct count AND graph it?
When you open the link, you can hit the ‘Copy Analysis’ button on the left to view the calculated fields.

Let me know if this could work for your case or if you have any additional questions

3

Hi Brett, thank you so much for the response. I have actually created the distinctcount calculation already and am looking to get the % of total count. Is there a way to hard-code the total instead? I’m trying:
distinctCountOver({user_id}, [{Users in Conversation Levels}], PRE_AGG)/1000 (assuming 1000 is the total), and it’s still giving me a syntax error.

4

Hi @zijingxu,

Apologies on the confusion! I believe the error is stemming from the the dimension for your distinctCountOver portion as Quick Sight does not allow you to nest a pre_agg calculation within another.

What if you tried running some of your calculated fields at the dataset layer to try and bypass the error?

5

Hi @zijingxu,

Following up here as it’s been awhile since we last heard from you on this thread; are you still working on this issue or were you able to find a work around in the interim?

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

Thank you!

6

Hi @zijingxu,

Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you