Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How do I get this total using set analysis?

I have a total orders text object that is set as

     =num(count(ORDER_NO), '#,##0')

So that is just telling me the number of orders total that I am pulling from the database. In addition to the total orders I have three subtotals of the orders 2 of which I have gotten easily. Those two are:

System Generated : =num(count({<ROLEUSER={'PSSCHED'}>} ORDER_NO), '#,##0')

Internal Orders: =num(count({<ROLENAME={'ASB_INTERNAL_USERS'}, ROLEUSER-={'PSSCHED'}>} ORDER_NO), '#,##0')

RoleUser is a listbox that contains a little over 719 different names of users, these users are the ones who have entered the orders

RoleName is a listbox that contains one object and that is ASB_INTERNAL_USERS, 440 users have the rolename 'ASB_INTERNAL_USERS' the remaining have no role name. The remaining users are the users that have entered the orders for the 3rd subtotal I am looking for. And that is where I am struggling to get the right expression for.

I have tried writing =num(count({<ROLENAME-={'ASB_INTERNAL_USERS'}>} ORDER_NO), '#,##0')

but it always gives me a zero, when I know the total is 183,025

I can get the total orders to display this by selecting the rolename then in the user listbox right clicking and selecting excluded which then grays out the role name.

Another possible way to get this 183025 is by adding the two subtotals and subtracting the sum from the total, but every time I try to sum a count() my expression does not work .

I hope I didn't make this to unclear, if anything could use some more explaining, I will do my best to clear it up.

Thank you

1 Reply
MK_QSL
MVP
MVP

Create below in your script..

IF(ROLENAME = 'ASB_INTERNAL_USERS', ROLENAME, ‘NONAME’) as ROLENAME

Now use

=num(count({<ROLENAME={'NONAME'}, ROLEUSER-={'PSSCHED'}>} ORDER_NO), '#,##0')

or

=num(count({<ROLENAME={'NONAME'}>} ORDER_NO), '#,##0')

Whatever is required…