topic Re: Expression Count in App Development

Expression Count

jpjust — Fri, 28 May 2021 12:57:27 GMT

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

Re: Expression Count

jwjackso — Fri, 28 May 2021 13:55:01 GMT

Try

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

Re: Expression Count

jpjust — Fri, 28 May 2021 15:11:31 GMT

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

Re: Expression Count

jwjackso — Fri, 28 May 2021 15:15:08 GMT

Try without the AGGR function

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

Re: Expression Count

jpjust — Fri, 28 May 2021 15:20:29 GMT

Perfect, it worked. Thanks for your help!

Re: Expression Count

jpjust — Fri, 28 May 2021 16:40:36 GMT

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

Re: Expression Count

jwjackso — Fri, 28 May 2021 17:14:50 GMT

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.

Re: Expression Count

jpjust — Fri, 28 May 2021 17:50:04 GMT

Yes, that's true.

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

Re: Expression Count

jwjackso — Fri, 28 May 2021 17:55:49 GMT

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

Re: Expression Count

jpjust — Fri, 28 May 2021 18:14:12 GMT

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