Simple aggregation as a calculated field

Q&A
, ,
1

Hi,

I am trying to show a Histogram of the number of users per account. my table is currently at the user_id level with the associated account in a separate column.

I want to roll this up in a calculated field, rather than doing it in the data source so I can reuse the data source for other purposes. The issue is that i can only provide a single field for the histogram and am not sure how to aggregate the data to the account level in a calculated field for this to work. If I partition by (or countOver) account, I believe that will count duplicates and if i do a count(user_id) that will just give me a single value and wont chart anything on the histogram.

As a side note, i get a VISUAL_CALC_REFERENCE_MISSING error when I try to use a countOver calculated field. Heres my formula:
countOver({USER_ID},[{ACCOUNT_ID}])

Thanks

2

Hi @asdf1 ,
I don’t know if I understood correctly you request and how your data schema is.
If it’s something like

user_id,account_id
U001,ACC01
U002,ACC01
U003,ACC01
U004,ACC01
U005,ACC01
U006,ACC02
U007,ACC02
U008,ACC02
U009,ACC02
U010,ACC03
U011,ACC03
...

(plus other fields obviously), can’t you just select user_id and account_id in the bar chart visual with distinct count as aggregation?

image
image2214×1116 229 KB

Andrea

3

Hi,

Thanks for the reply! I am not trying to show the account on the x axis, i am trying to the show the frequency of the count in a histogram. So on the X-axis I would have 1,2,3… and the count of customers with that many accounts on the Y-axis. Does that help clarify?

4

I was able to resolve this. I had a syntax error in my calculated field.

1 Like
5

Hi @asdf1 , now it’s clear you need.
Just seen also that you solved this one :slight_smile:

Is the correct calculated field this one:

distinctCountOver({account_id},[{user_id}],PRE_AGG)

Thank you,
Andrea

6

Hi @andrepgn

Apologies. I spoke too soon. I am still getting duplicate data. Yes I use the formula you listed above but when i put it into the histogram, it sums the rows so you get something like this

user count
user1 2
user1. 2
user2. 3
user2. 3
user2. 3

so i get count 2 two times for user1, when it should only be counted once. Do I need to apply another calculated field to achieve a single row per customer like below?

user count
user1 2
user2. 3

7

Please note that I’ve not only changed the two fields position in the formula, but I’ve also used the distinctCountOver. I think that should work, have you tried with that?

Andrea

8

Yes, I used the distinctcountover and that doesnt seem to change anything. Reversing the order of the fields sets everything to 1, weirdly.

9

Hi @asdf1, can you show us better what you are doing and a sample of the data (or just the schema)?
Seems a strange behavior, we are missing something! :slight_smile:

Andrea

10

Hi @asdf1,

Just checking in since this thread hasn’t received a response in a while. Were you able to see andrepgn’s recent question, or did you find a solution yourself in the meantime? If not, please feel free to elaborate on any persistent issues that you are currently facing. If we do not hear back within the next 3 business days, I’ll go ahead and close out of this topic.

Thanks!

11

Hi @asdf1,

Since we have not heard back, I’ll go ahead and close this topic. However, please feel free to post again in the Quick Community and link this thread for any relevant information!