topic Re: Expression Question in App Development

Expression Question

jpjust — Tue, 22 Jun 2021 18:06:47 GMT

Hi All,

I a m in need of expression assistance here:

I would like the following 2 expressions to go in one expression to calculate the SUM. What would be the better way?

I tried many combinations but I did not get the desired results.

if(createdtimestamp1 = '$(v_Last_Creation)'

sum(If(ISNULL(Today() -[User Access Last Used]),1,0))

Thanks

Re: Expression Question

GaryGiles — Tue, 22 Jun 2021 18:12:25 GMT

Can you explain what is intended by ISNULL(Today() -[User Access Last Used])? Is [User Access Last Used] a date field? If so, the expression would only return true if [User Access Last Used] was null().

Re: Expression Question

jpjust — Tue, 22 Jun 2021 18:25:12 GMT

Thanks for your response, Gary.

In a table with few other fields, I have a field called Idle Days and the expression is =If(ISNULL(Today() -[User Access Last Used]),'NEVER',Days_not_used,)

So I am trying to get the count of "NEVER" and display it on the KPI.

Yes, [User Access Last Used] is a date field.

To display the count of "NEVER" on KPI, I am trying the following expression but the numbers are going way OFF (330 records instead of 30 records) ,May be an simple approach available?

=sum(If(ISNULL(Today() -[User Access Last Used]) and createdtimestamp1 = '$(v_Last_Creation)',1,0))

Thanks

Re: Expression Question

GaryGiles — Tue, 22 Jun 2021 18:54:19 GMT

It still isn't clear to me what ISNULL(Today() -[User Access Last Used]) is accomplishing.

Have you tried some like this in your KPI:

Count({$<[User Access Last Used={*},createdtimestamp1={'$(v_Last_Creation)'}>} Distinct [User])

[User] would be the unique identifying field for the user.

Re: Expression Question

jpjust — Tue, 22 Jun 2021 19:39:47 GMT

Sure, Let me provide some more details.

Here is an table with sample data.

UserName User Access Last Used Idle Days

Michael 03/21/2021 90

Mark 06/01/2021 23

Tim - NEVER

Daniel - NEVER

Idle days field is calculated as If(ISNULL([User Access Last Used]),'NEVER',Days_not_used,)

Now as I mentioned, I am trying to get the count of Idle Days = NEVER and display it on the KPI.

I tried your count function with set analysis but did not bring the desired results.

Thanks

Re: Expression Question

GaryGiles — Tue, 22 Jun 2021 19:47:46 GMT

Count({$<[Idle Days]={‘NEVER’}>} Distinct [User])

Re: Expression Question

jpjust — Tue, 22 Jun 2021 19:51:27 GMT

Idle days is an label for a column in table with an expression as below.

If(ISNULL([User Access Last Used]),'NEVER',Days_not_used,)

How can insert this into the set expression?

Thanks

Re: Expression Question

dcheung3388 — Wed, 23 Jun 2021 01:33:35 GMT

Hi,

In your script Load, I assume your [User Access Last Used] is a field in the dataset,

1. Create a New column => to test the IsNull([User Access Last Used]) => 1 = Null, 0= Not Null

2. Then Sum this column in your KPI

This would be the most simple way of doing this.

Hope it helps.