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

[punitpopli]

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.

[marcus_sommer]

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)

[punitpopli]

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? 

[marcus_sommer]

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.

[punitpopli]

hi @marcus_sommer 

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

[marcus_sommer]

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

[punitpopli]

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) 

[marcus_sommer]

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

1 = ?

2 = ?

3 = ?

[punitpopli]

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.

[marcus_sommer]

Unfortunately no. I thought on real numbers.