Count in one calculated field

Hi, I need help with the below example.

I am using CMSUSERID & RESOURCEID. based on that I need to count as described in the image.
Currently, It is giving me a total count of 1(min) which should be the total count(i.e. 1+1+5+11+2+3+1+2…).

Thanks in advance.

I believe you need to partition by resourceID.

distinctCountOver({RESOURCEID},[{RESOURCEID}],PRE_AGG)
1 Like

@Max your calculated field gives me a total count of 1520. I need the total no of distinct resources launched by district users. So, if you see in the below image and example.

User1 - 9e4 (1),
User2 - 538 (1),
User3 -

538 (1), 
b48 (1),
c22 (1),
c2e (1), 
f5c (5 times attempted, but as it is unique I need to count it as 1)

---- subtotal should be 5 ----
User4 -

ba8 (2 times attempted, but as it is unique I need to count it as 1)
c227 (2 times attempted, but as it is unique I need to count it as 1)

---- subtotal should be 2 ----

So, total count 1 +1 + 5 + 2…

is this visual and count achievable or not? :neutral_face:

Is it just then partitioned by user?

distinctCountOver({RESOURCEID},[{CMSUSERID}],PRE_AGG)

1 Like

Yes, You are right. but I need a total count in just one calculated field as I have to compare it with other counts.

using the below formula I am getting 533. But As I am using POST_AGG_FILTER, I am not able to manage the count in one calculated field.

sumOver(distinct_count(RESOURCEID), [CMSUSERID], POST_AGG_FILTER) - #test1
distinctCountOver({RESOURCEID},[{CMSUSERID}],PRE_AGG) - #test2

Is there any way? Refer to the below screenshot. You can refer to the first screenshot also for better understanding.

What do you mean “manage the count in one calculated field”?

Is one correct but you can’t use it because of aggregations?

manage the count in one calculated field means, I need to divide that count(533) by total user attempted resource count(1520). So, the as a final output I need the value in percentage.

So, If I use the #test1 with cmsuserid then it is giving me total count as 533. But I need that count without mapping the calculated field #test1 with cmsuserid.

Something like this?

sumOver(distinct_count({session_id}), [{client_id}], POST_AGG_FILTER)/distinct_count({client_id})

If you use PRE_AGG you can use it in like a KPI and not have to reference cmsuserid

1 Like