Calculating and filtering in one row

Question & Answer
, ,
1

Hello,

I have a data set with customerId, date and amounts (See Table 1)
sample

I’d like to count the unique users who exceed 100 USD per month just like Table 2 but symmarized in one row (like Table 3)

I’ve just tried building distinct_count formula (distinct_countIf(customerId,amounts>0) or (distinct_countIf(customerId,sum(amounts)>0) but the first one it doesn’t work and the second one is not allowed.

So, is there any formula that can I use?

Thanks

1 Like
2

Hey @KLIKIN ,

great that you provides an Arena Link.
This formula should work: ifelse(sum(amounts,[customerId, date])>100,distinct_count(customerId,[customerId,date]), NULL)
It feels a little bit unintuitive, but when playing in Arena it worked for me.

BR
Robert

1 Like
3

Thanks. it works for me