Expression Count

[jpjust]

Hi All,

I might need a expression help.

I have an expression below.

If(ISNULL(Today() -[User Access Used ASIA]) and ISNULL(Today() -[User Access Used EMEA]),'Notused', 'used')

The field from the above expression displays values in a table as below.

UserName       User Access Used ASIA  User Access Use EMEA    Flag

Matthew                                                                                                              Notused

Mike                       05/15/2021                                                                         Used

Steve                                                                              05/25/2021                 Used

Bob                                                                                                                          Notused

Now I want to the count  Flag='Notused' and display it on the kpi. I know this is a simple one.

I tried writing expressions but not bringing the correct count. Can some one please assist?

Thanks

[jwjackso]

Try

Sum(Aggr(If(ISNULL(Today() -[User Access Used ASIA]) and ISNULL(Today() -[User Access Used EMEA]),1,0),UserName))

[jpjust]

Thanks for that expression.

The output is very odd, comes to 17,000 while I expect just 39

The number of records in the table is around 200

[jwjackso · Accepted solution]

Try without the AGGR function

Sum(If(ISNULL(Today() -[User Access Used ASIA]) and ISNULL(Today() -[User Access Used EMEA]),1,0))

[jpjust]

Perfect, it worked. Thanks for your help!

[jpjust]

Hi - I might need one more help if you don't mind

I have an snippet of an expression as below. Saved this as fieldA (Master Item)

=If(ISNULL(Today() -[User Access Used ASIA]) and ISNULL(Today() -[User Access Used EMEA]),'Never',
If(ISNULL(Today() -[User Access Used ASIA]),Today() - [User Access Used EMEA],
If(Today() -[User Access Used ASIA] < Today() - [User Access Used EMEA],Today() -[User Access Last Used]
))

Now this expression outputs numbers as 10, 45, 78,60 etc.,

I am trying to derive another KPI field that should show number of records greater than 50 from the above expression / field

I am trying all possibilities but not getting what I want. I wanted to use the above field just as an simple KPI expression below but I am not able to refer to master item.

Sum(If([FieldA]>50,1,0))

If you can shed some light would be helpful!

Thanks

[jwjackso]

As far as I'm aware, you cannot use a master item within another expression.  You would need to create another master item for the summation.

[jpjust]

Yes, that's true.

So how can I get an count of more than 50 from expression as below?

=If(ISNULL(Today() -[User Access Used ASIA]) and ISNULL(Today() -[User Access Used EMEA]),'Never',
If(ISNULL(Today() -[User Access Used ASIA]),Today() - [User Access Used EMEA],
If(Today() -[User Access Used ASIA] < Today() - [User Access Used EMEA],Today() -[User Access Last Used]))

[jwjackso · Accepted solution]

Sum(If((If(ISNULL(Today() -[User Access Used ASIA]) and ISNULL(Today() -[User Access Used EMEA]),0,
If(ISNULL(Today() -[User Access Used ASIA]),Today() - [User Access Used EMEA],
If(Today() -[User Access Used ASIA] < Today() - [User Access Used EMEA],Today() -[User Access Last Used]))) > 50,1,0))

[jpjust]

Perfect. Thanks a lot. Sure, I learnt some thing.