? set analysis; Count distinct whith calculation of days difference

[#1]

Hi, working my way through set analysis

Im trying to count the occurences in my data set where the number of days between two date fields is more than 21 days, I tried something like:

=Count({<[Dt Cons Rec solution]-WO_Date>{'21'>} DISTINCT [Unique reference number#])

But this is not doing the trick, any suggestions?

mistake in expression posted: i used

=Count({<[Dt Cons Rec solution]-WO_Date>{'21'}>} DISTINCT [Unique reference number#])

[#2]

Perhaps this will work:

if(Dt Cons Rec solution]-WO_Date > 21, count(Distinct [Unique reference number#]))

Best,

Matt

[#3]

Thanks for the suggestion Mattsies, afraid though this is not doing the trick. Any more suggestions?

[#4]

Rob Wunderlich has a great document in his cookbook that will help you.  If you go to his site here: http://robwunderlich.com/downloads/, download the Cookbook and look in Cookbook.qvw.  The description is "Count days in a transaction using interval match".

[#5]

Thanks for the suggestion Mattsies, afraid though this is not doing the trick. Any more suggestions?

[#6]

Hi Rebecca,

thanks for this site, i tried to solve it in the frontend, where scription would make things easier. Just hoped to see how this could be solved via Set Analysis in the front end....

[#7]

Try something like this:

Count(DISTINCT if(EndDate-StartDate>=21, ID))

[#8 · Accepted solution]

Sure, perhaps I was missing a set of brackets:

if(Dt Cons Rec solution]-WO_Date > 21, count(DISTINCT([Unique reference number#])))

This sintax returns accurate results on a data set of mine, so hopefully will work. If not, you could always post a sample qvw for us to try it out on.

Best,

Matt