count the ID's using calculated measure

[sjar1290]

Hi All,

I am trying to get the unauthorization count from the below table.

let me tell you what unauthorization count is: where ever Access A is 'NO and Access B is 'YES' that's unauthorized.

I need to find the that count by user ID, for Ex: if the below table has 1000 rows, user 1 may have 10 unauthorized count and user 2 has 15 unauthorized count and so on. I need to dispaly that count in KPI  and in a Table as Unauthorized count

I have table in Qlik sense like the below table and I am selecting the user Id using the user id filter.

Fields	Access A	Access B
Field 1	Yes	No
Field 2	Yes	No
Field 3	No	Yes
Field 4	Yes	No
Field 5	Yes	No
Field 6	Yes	No
Field 7	No	Yes
Field 8	No	Yes
Field 9	Yes	No
Field 10	Yes	No

but when I try to find the count, I am getting the result like below (I have a User Id Filter)

user Id	Unauthorized count
user 1	20
user 2	0
user 3	0

but when I am selecting the users user 1 , user 2 and user 3 individually its displaying the respective results, when I select the multiple user Id's  I am getting the wrong result like the above table.

please help me with the expression

any help would be appreciated.

Thank you!

[Vegar]

Hi

Are you trying to solve this in the load script or in the GUI?

What is the calculation that you use in order to get the Unauthorised Count?

[Mark_Little]

HI @sjar1290 

I assume you have used the set analysis of

COUNT({<{Access A}= {'No'}, [Access B]={'Yes'}>}DISTINCT Fields),

This should give you the expected results, unless there is some strange data modelling or other calculations on your front end tables

[sjar1290]

Hi Vegar,

I am exactly using the same formula , but the Access A  and Access B are the calculated measures in my APP

let me tell you the scenario:

I am comparing 2 columns column 1, column 2 with  column name Fields

for Access A I am using the formula SUM(IF(column 1 = Fields,1))

for Access B I am using the formula SUM(IF(column 2 = Fields,1))

I just want to display the unauthorized count in a KPI and as I said I was trying to display the UserID like below table , but I am not getting the result.

user Id	Unauthorized count
user 1	20
user 2	0
user 3	0

I am supposed to get the result like below but I am getting the result like the above table

user Id	Unauthorized count
user 1	20
user 2	15
user 3	25

 and in the KPI I need to show the result '65'

I am trying to add the user Id to the result table , and I am using the calculated dimension which is making the app very very slow, is there any other expression other than the calculated dimension to speed up the App

calculated dimension:

if(Fields<>column 1 and Fields=column 2, [User ID], null())

If I use this expression =if([Access A]=0 and [Access B]=1, [User ID], null()), I am getting Null values in the User Id

Fields	Access A	Access B	user ID
Field 3	No	Yes	user1
Field 3	No	Yes	user2
Field 3	No	Yes	user3
Field 7	No	Yes	user4
Field 7	No	Yes	user5
Field 8	No	Yes	user8
Field 8	No	Yes	user2
Field 8	No	Yes	user4

please help me.

Thank you!