Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

DH

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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)


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

Try this...

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

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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)


talk is cheap, supply exceeds demand
Not applicable
Author

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 !!

DH