Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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, ', ')
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)
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, ', ')
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.
May be try this:
If(Count(DISTINCT TOTAL <LaptopField> ServerGroupField) = 2, 'Yes', 'No')