Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
ben2roberts
Contributor

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
MVP & Luminary
MVP & Luminary

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

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
10 Replies
gmoraleswit
Contributor II

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

try this:

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

MVP & Luminary
MVP & Luminary

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

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

See attached qvw.


talk is cheap, supply exceeds demand
ben2roberts
Contributor

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

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.

ben2roberts
Contributor

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

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.

MVP & Luminary
MVP & Luminary

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

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
Contributor II

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

try this:

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

ben2roberts
Contributor

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

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

ben2roberts
Contributor

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

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

MVP & Luminary
MVP & Luminary

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

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