Expression Question

[jpjust]

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

[GaryGiles]

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().

[jpjust]

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

[GaryGiles]

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.

[jpjust]

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

[GaryGiles]

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

[jpjust]

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

[dcheung3388]

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.