Building an administrative need help with calculated fields

working on the user analysis tab, I am trying to get a count of Active and Inactive users. I created a calculated field to get the status of each user based on the following expression

ifelse(
dateDiff({Max Event Time}, now(), ‘DD’) < 30, ‘Active’,
dateDiff({Max Event Time}, now(), ‘MM’) < ${InActivityMonths}, ‘Active’,
‘Inactive’
)

now when I try to count distinct active users and Inactive users I get the following error distinct_countIf(email, UserStatus = ‘Active’)

Hi @olusegun83 - Good to see your post after a long time. To understand this issue, can you please share the sample input data and expected output. This will help in understand the problem correctly.

Tagging @David_Wong for his advise as well.

Regards - Sanjeeb

Thanks @Sanjeeb2022 not sure I follow your ask. My use case is I am trying to count all active and inactive users based on the logic above but I run into that error seen above. Thanks

Hi @olusegun83 - Ok, I thought of you have some input data and put some logic to calculate the active vs inactive users and face the issue. Is the analysis you are creating with the data from QuickSight generated? Need more context on your input data set.

Regards - Sanjeeb

Hi @Sanjeeb2022 thanks i created with a mock up data. CSV file.

Hi @olusegun83 - Is it possible to share the sample from the mock up data so that we can replicate at our end.

Regards - Sanjeeb

Hi @olusegun83,

Is {Max Event Time} a calculated field? If so, can you please show the calculation?

1 Like

hello there max event is a calculated field as seen

max({event_time})

Try to use maxOver instead of max. The issue with dateDiff({Max Event Time}, now(), ‘DD’) is that {Max Event Time} is aggregated but now() isn’t. You’re trying to substract an aggregated value from a single row value.

Change {Max Event Time} to maxOver({event_time}, [user_id], PRE_AGG).

Replace user_id by the unique user identifier that you have in your dataset.

i tried using this as suggested, but now getting this error At least one of the arguments in this function does not have correct type. Correct the expression and choose Create again.

maxOver({event_time}, {email}, PRE_AGG)

You need square brackets around your {email} field.

maxOver({event_time}, [{email}], PRE_AGG)

2 Likes