Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This is a follow on from feedback in discussion Exclude Results from Other Field in Select Excluded and Set Analysis: Show Excluded and Refine Results.
I have attached a sample QVD. I am convinced that the solution here is really quite simple but it escapes me.
I am trying to create a list of bankers where they have not done any work for my team. I have 2 data sources, 1 a list of all work by Request Id, with additional fields of banker name, year and month of work. The second source is a list of all bankers and the region they work in.
I have created a pivot table with an expression of IF(COUNT({$<Month=,Year=,[Banker Region]=>} [Request Id])<1,'1','0')
Without any filters, this expression returns the result of 1 for any bankers who have 0 request IDs in my first source (list of work) and 0 where they have done work. I then use exclude zero values in presentation tab to remove those bankers.
The problem I face is when I try to further refine the results, by selecting a particular region or year or month. The list of 'not worked' is fixed and does not change. I believe this is the impact of the $<[field]=> syntax. If I remove this and just use the If with no set analysis, the result is correct with no selection for year / month etc, but as soon as I make a selection, e.g. region = MET, then all bankers from all regions except MET are shown.
Perhaps this one then:
count({1<[Banker Region]=p([Banker Region]), [Banker Name]=e({<[Request Id]={'*'}>}[Banker Name])>}1)
try this:
count({$<[Banker Name]={"=COUNT([Request Id])=0"}>} 1)
Try count({1<[Banker Name]=e({<[Request Id]={'*'}>}[Banker Name])>}1)
See attached qvw.
Thanks Gabriela Morales but that did not work exactly as I was hoping.
Seems to be fine when you filter a region, but any additional filters cause zero results.
e.g. If I select UK there are 5 bankers. A, B and C have done work, H and I have not.
If I then look at 2014 only for year, the result = 0, whereas actually H and I have also not done any work in 2014.
I think this is the tricky part as I want to overlay those two (and there could be others) on top of the region selection.
Thanks Gysbert Wassenaar but this did not work as expected.
Using your file, if I select a region (e.g. UK team), the no work completed box contains all bankers that have not done any work, regardless of the region they come from.
I need to be able to show just those bankers from that region who have not done any work.
Additionally, there is a time component - my real data goes back 3 years and in practice it is only 2014 we are interested in. Sometime we want to look at a case like: Who has worked with us in the last 3 months of 2014 who is in the UK time, with the reverse of that in who out of those UK team bankers has not worked with us in that 3 month period in 2014.
Perhaps this one then:
count({1<[Banker Region]=p([Banker Region]), [Banker Name]=e({<[Request Id]={'*'}>}[Banker Name])>}1)
try this:
count({$<Year=, Month=, [Banker Name]={"=COUNT({$<Year=, Month=>}[Request Id])=0"}>} 1)
Thanks for the help Gabriela. The code that Gysbert recommended below solved my problem but many thanks for your help.
Perfect! Thanks Gysbert Wassenaar - I have been scratching my head on this one for weeks!
Can I just get one last bit of help in understanding what is going on here. My assumed logic below:
count({1< // count for all possible values
[Banker Region]=p([Banker Region]), //where possible values (P) are in the banker region selection (enforces scope to be banker region only)
[Banker Name]=e({<[Request Id]={'*'}>} //where banker name is different (E) from current selected request Ids (effectively means the request Ids associated with the current year / month / any other filter)
[Banker Name])> //count of the banker name
}1) // not sure what this part does?
Thanks,
Ben
count( --just an aggregation function to return a number other than zero so the row won't be suppressed
{1< --ignore all current selections,
[Banker Region]=p([Banker Region]) --but do use the current selection in the Banker Region field
[Banker Name]=e({<[Request Id]={'*'}>} [Banker Name]) --and exclude the bankers that given the current selection do have a request id.
1) --count 1 or 2 or any number will do as will any aggregation function like sum, max, min, avg...etc.