topic Re: more complex set modifiers - define a filter and ignore the listbox in QlikView

more complex set modifiers - define a filter and ignore the listbox

Sun, 17 Feb 2013 05:16:01 GMT

hi Guys

wondering if someone can help please. This is a follow up question to another
thread I had but it is a new question and for clarity am keeping it separate. I thought using a {1} would
solve this issue but it appears not.

I have put together a simple qvw and data set for added clarity.

Here's what I aiming to achive without success.

Show a distinct count of people that had sales > 100 over multiple lines for a given day

If I filter on that person - the count should not be affected. I have tried multiple variations
but without success

Would be grateful if someone can help. The code I am using as you can see in the qvw is

=count({<Salesrep={'=Sum(Revenue)>100'},TransDate={'$(=date(max(TransDate)))'}>} DISTINCT Salesrep)

How can I ensure that if I place a filter on Salesperson in the listbox, that it does not affect this
calculation but still allow me to choose a different date.

thank you in advance

Re: more complex set modifiers - define a filter and ignore the listbox

phaneendra_kunc — Sun, 17 Feb 2013 08:45:49 GMT

Try this...

=count({<Salesrep={'=Sum({<Salesrep=>}Revenue)>100'},TransDate={'$(=date(max(TransDate)))'}>} DISTINCT Salesrep)

Re: more complex set modifiers - define a filter and ignore the listbox

Gysbert_Wassenaar — Sun, 17 Feb 2013 13:31:14 GMT

You have to limit sum(Revenue) to the choosen date too:

=count({<Salesrep={'=Sum({<TransDate={"$(=date(max(TransDate)))"}>} Revenue)>100'},TransDate={'$(=date(max(TransDate)))'}>} DISTINCT Salesrep)

Re: more complex set modifiers - define a filter and ignore the listbox

Sun, 17 Feb 2013 18:00:12 GMT

Phaneedndra & Gysbert

thank you both every much for your speedy replies - you collectively solved my situation. I had tried so many variations and although I was close my exact syntax was off. In the case of the saleperson filter I had used Salesperson=, instead of Salesperson=

In the case of the date filter I had used single quotes instead of double quotes and that made the difference. My final code (or I should say your combined final code) is as follows :

=count({<Salesrep={'=Sum({<Salesrep=,TransDate={"$(=date(max(TransDate)))"}>} Revenue)>100'},TransDate={'$(=date(max(TransDate)))'}>} DISTINCT Salesrep)

Thank you both very much again - greatly apprciated !!