topic Re: Facing issue in finding in distinct count of a dimension based on metric condition in App Development

Facing issue in finding in distinct count of a dimension based on metric condition

punitpopli — Thu, 30 Mar 2023 13:32:20 GMT

Hi All,

Please find the code required for this problem statement -

App:
Load * Inline [
Id,Name
1,App Name1
2,App Name2
3,App Name3
4,App Name4
5,App Name5
];

Log:
Load * Inline [
Id,UserId
1,User1
2,User2
3,User3
4,User4
5,User5
1,User6
2,User4
3,User5
4,User2
5,User7
1,User8
2,User9
3,User4
4,User5
5,User2
];

Employee:
Load * Inline [
UserId,Desk,Role
User1,Desk1,Role1
User2,Desk2,Role2
User3,Desk3,Role3
User4,Desk4,Role4
User5,Desk5,Role5
User6,Desk3,Role6
User7,Desk4,Role7
User8,Desk8,Role3
User9,Desk2,Role4
User10,Desk10,Role10
User11,Desk3,Role11
User12,Desk4,Role12
User13,Desk13,Role13
User14,Desk14,Role3
User15,Desk15,Role4

];

Take a pivot table and use Role and Desk and dimension while Name as a filter selection. Metric should be -

1. Count(distinct {<Id ={"1","2","3","4","5"}>}UserId) - this will provide the list who accessed the app

2. Count(distinct{<Name=>} UserId) - this will give all the users available

3. If(Count(distinct {<Id ={"1","2","3","4","5"}>}UserId) =0,1) - this condition will be used to find all the desk under which none of the users had accessed the app when we select Name as a filter

when am trying aggregation on #3 above its not providing the actual count

Any help here is much appreciated.

Re: Facing issue in finding in distinct count of a dimension based on metric condition

marcus_sommer — Thu, 30 Mar 2023 14:30:28 GMT

A direct counting of NULL isn't possible but you may try it with indirect ways - maybe something like:

Count(distinct{<Name=>} UserId) - Count(distinct {<Id ={"1","2","3","4","5"}>}UserId)

Re: Facing issue in finding in distinct count of a dimension based on metric condition

punitpopli — Thu, 30 Mar 2023 16:47:30 GMT

thank you Marcus for your response.

I am looking to use the calculation to display # of desk as a KPI and by using the difference will be unable to achieve the KPI

Is their any other way I can find the #of desk and user under the same?

Re: Facing issue in finding in distinct count of a dimension based on metric condition

marcus_sommer — Fri, 31 Mar 2023 06:44:45 GMT

You may need to extend the above mentioned main-logic to count also the Desk and the combination of UserId and Desk, like:

count(distinct Desk)

count(distinct Desk&UserId)

as parts of the final calculation.

Re: Facing issue in finding in distinct count of a dimension based on metric condition

punitpopli — Fri, 31 Mar 2023 12:13:10 GMT

hi @marcus_sommer

am unable to follow, would you mind sharing the expression what you are referring too?

Re: Facing issue in finding in distinct count of a dimension based on metric condition

marcus_sommer — Fri, 31 Mar 2023 12:21:35 GMT

In regard to your shown example of inline-tables - what are the expected results for your expressions?

Re: Facing issue in finding in distinct count of a dimension based on metric condition

punitpopli — Fri, 31 Mar 2023 12:28:47 GMT

By Using Expression #1 and #2 what I am looking for is -

Out of total 15 users only 3 are accessing App 1/2 and what are the # of desk which has users are not accessing the application and total user under the same desk(s)

Re: Facing issue in finding in distinct count of a dimension based on metric condition

marcus_sommer — Fri, 31 Mar 2023 13:41:17 GMT

Sorry, it's not clear which results are expected for expression:

1 = ?

2 = ?

3 = ?

Re: Facing issue in finding in distinct count of a dimension based on metric condition

punitpopli — Fri, 31 Mar 2023 14:13:47 GMT

Exp # 3 is based on Exp #1 -

1. Count(distinct {<Id ={"1","2","3","4","5"}>}UserId) -

2. Count(distinct{<Name=>} UserId) - this will give all the users available

3. If(Count(distinct {<Id ={"1","2","3","4","5"}>}UserId) =0,1)

I wan to find all the desks and User under that desk which have not accessed the app using aforementioned expressions (probably #1 or #3) While #2 provides the laundry list of all the users in the system

Please let me know if aforementioned explanation helps.

Re: Facing issue in finding in distinct count of a dimension based on metric condition

marcus_sommer — Fri, 31 Mar 2023 16:03:18 GMT

Unfortunately no. I thought on real numbers.