Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Try this...
=count({<Salesrep={'=Sum({<Salesrep=>}Revenue)>100'},TransDate={'$(=date(max(TransDate)))'}>} DISTINCT Salesrep)
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)
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