Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
jpjust
Specialist
Specialist

Expression Question

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

7 Replies
GaryGiles
Specialist
Specialist

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
Specialist
Specialist
Author

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
Specialist
Specialist

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
Specialist
Specialist
Author

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
Specialist
Specialist

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

jpjust
Specialist
Specialist
Author

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
Partner - Contributor III
Partner - Contributor III

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.