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.