Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Use additional filters to refine results where Count = zero

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.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Perhaps this one then:

count({1<[Banker Region]=p([Banker Region]), [Banker Name]=e({<[Request Id]={'*'}>}[Banker Name])>}1)


talk is cheap, supply exceeds demand

View solution in original post

10 Replies
gmoraleswit
Partner - Creator II
Partner - Creator II

try this:

count({$<[Banker Name]={"=COUNT([Request Id])=0"}>} 1)

Gysbert_Wassenaar

Try count({1<[Banker Name]=e({<[Request Id]={'*'}>}[Banker Name])>}1)

See attached qvw.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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.

Gysbert_Wassenaar

Perhaps this one then:

count({1<[Banker Region]=p([Banker Region]), [Banker Name]=e({<[Request Id]={'*'}>}[Banker Name])>}1)


talk is cheap, supply exceeds demand
gmoraleswit
Partner - Creator II
Partner - Creator II

try this:

count({$<Year=, Month=, [Banker Name]={"=COUNT({$<Year=, Month=>}[Request Id])=0"}>} 1)

Anonymous
Not applicable
Author

Thanks for the help Gabriela. The code that Gysbert recommended below solved my problem but many thanks for your help.

Anonymous
Not applicable
Author

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

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand