Count If Expression

[dawgfather]

I am loading a SQL query into a table. I have records for items (laptops) that connect to different servers of different types, so there are likely multiple entries for some laptops.

I want to create an expression to count the number of laptops that have connected both of the 2 types of servers ("old" and "new") - but they have to have connected to BOTH types. If a laptop connects to one or the other, don't count it. If there is a record of it connecting to one and then also an entry connecting to the other, then count those (even better if I could come up with a Yes/No type expression to say it's connected to both, but a count will do).

Some sample data and the last column is whether I would expect it to be counted or not.

I should end up with a count of 3 (DEF123, ABC345 and DEF456) that connected to both types.

In my head my logic is something like count LAPTOP if there is a record matching (where) ServerGroup = 'old_servers' AND where there is a record matching (where) ServerGroup = 'new_servers'. But I cannot seem to get anything. Everything I do seems to do this at each record's level so the counts are always 1.

Laptop field	Server field	ServerGroup field	LastSeenDate field	LastSeenOld expression	LastSeenNew expression	Desired outcome (comment)
ABC123	Old1	old_servers	11/2/2016	11/2/2016	-	Not counted
DEF123	Old2	old_servers	11/4/2016	11/4/2016	-	Counted
DEF123	New5	new_servers	11/6/2016	-	11/6/2016	Counted
ABC345	Old1	old_servers	11/5/2016	11/5/2016	-	Counted
ABC345	New6	new_servers	11/6/2016	-	11/6/2016	Counted
DEF456	Old3	old_servers	11/5/2016	11/5/2016	-	Counted
DEF456	New7	new_servers	11/8/2016	-	11/8/2016	Counted
DEF678	New8	new_servers	11/8/2016	-	11/8/2016	Not counted
ABC789	New7	new_servers	11/9/2016	-	11/9/2016	Not counted

[vishsaggi]

Try this in your Pivot table and supress zero values in presentation tab.

Dim: All your dimentsion

Expr: = Sum(Aggr(Count({< ServerGroupfield = {'old_servers','new_servers'}>} Laptopfield), Laptopfield))

Or

Expr:  Count(TOTAL < Laptopfield> {< ServerGroupfield = {'old_servers','new_servers'}>} Laptopfield)

[sunny_talwar · Accepted solution]

May be this:

If(Count(DISTINCT ServerGroupField) = 2, 'Yes', 'No')

in Text box obect, may be like this:

For count

Count(DISTINCT {<LaptopField = {"Count(DISTINCT ServerGroupField) = 2"}>} LaptopField)

For list

Concat(DISTINCT {<LaptopField = {"Count(DISTINCT ServerGroupField) = 2"}>} LaptopField, ', ')

[dawgfather]

The option for "If(Count(DISTINCT ServerGroupField) = 2, 'Yes', 'No')" worked great in a pivot.

I could then collapse the other details and get a Yes/No. Only hitch is that I have no way to sort on those once the details are expanded because then each line item only has 1 ServerGroupField and therefore the IF statement results in a "No". But it gets me much closer than I was.

Thank you.

[sunny_talwar]

May be try this:

If(Count(DISTINCT TOTAL <LaptopField> ServerGroupField) = 2, 'Yes', 'No')