topic Re: Use additional filters to refine results where Count = zero in QlikView

Use additional filters to refine results where Count = zero

Anonymous — Fri, 17 Oct 2014 16:14:42 GMT

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.

Re: Use additional filters to refine results where Count = zero

gmoraleswit — Fri, 17 Oct 2014 20:13:34 GMT

try this:

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

Re: Use additional filters to refine results where Count = zero

Gysbert_Wassenaar — Sat, 18 Oct 2014 18:00:48 GMT

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

See attached qvw.

Re: Use additional filters to refine results where Count = zero

Anonymous — Mon, 20 Oct 2014 16:39:04 GMT

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.

Re: Use additional filters to refine results where Count = zero

Anonymous — Mon, 20 Oct 2014 16:43:40 GMT

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.

Re: Use additional filters to refine results where Count = zero

Gysbert_Wassenaar — Mon, 20 Oct 2014 18:44:06 GMT

Perhaps this one then:

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

Re: Use additional filters to refine results where Count = zero

gmoraleswit — Mon, 20 Oct 2014 18:45:35 GMT

try this:

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

Re: Use additional filters to refine results where Count = zero

Anonymous — Tue, 21 Oct 2014 11:09:27 GMT

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

Re: Use additional filters to refine results where Count = zero

Anonymous — Tue, 21 Oct 2014 11:14:31 GMT

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

Re: Use additional filters to refine results where Count = zero

Gysbert_Wassenaar — Tue, 21 Oct 2014 11:40:31 GMT

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.

Re: Use additional filters to refine results where Count = zero

Anonymous — Tue, 21 Oct 2014 11:43:02 GMT

Top man – many thanks.