show values between dates

[#1]

Does anyone have a solution for counting a value which is valid between the start date and the end date. See the between date qvw.

I only want to count valid values within the selected YEARMONTH_PER field.

• count(distinct ID)if(DATE_PER > Startdate AND DATE_PER <= Enddate)

the syntax above is not working 😞

Hope someone has a solution or knows how to handle this kind of situations..

[johnw · Accepted solution]

I'm not sure I understand your application, but it may be as simple as fixing your syntax:

count(distinct if(DATE_PER > Startdate AND DATE_PER <= Enddate,ID))

[#3]

Correct syntax is as below

count(distinct if(DATE_PER > Startdate AND DATE_PER <= Enddate,ID,o))

Regards,

Amit

[#4]

Thanks it works! but with more then 1 million records this is getting slow.... any other solutions?

[johnw]

If you have version 8.5, you should be able to use set analysis to accomplish the same thing. Set analysis should run much faster. Here's an expression that works as long as you select only a single DATE_PER at a time. If you want more than one, I can try to figure out something more complicated that will handle that as well.

count({<Startdate={"<$(=DATE_PER)"} Enddate={">=$(=DATE_PER)"}>} distinct ID)

[#6]

thanks, this works great with much better performance! I would like to select more then just one date so if you have some time to figure this out it would be great! I can't find much about records with a valid date range between two dates while it's quite a common thing in BI land.

[johnw]

Actually, let me take a step back. If I underestand what you're trying to do, the best and fastest way is probably to just link the tables together using an intervalmatch. Is the attached what you're looking for?

[#8]

This really looks like my solution :-)) I'm going to test some things in my app! Many thanks for this one John!

[#9]

Almost what I'm looking for ;-). When you select a certain amount of time it shows all versions of the ID which lays in that certain time.

I only want to show (count) the latest version of the ID laying in the selected time.

Example:

Selected time is between 1-1-1990 and 1-1-1995

ID version startdate enddate

100 1 1-1-1990 1-1-1992

100 2 2-1-1992

I only want to show (count) version 2. Hope I make myself clear 😛 Thanks for helping me out!

[johnw]

I don't know if this would work for your actual application, but instead of having Startdate and Enddate as dimensions, you could have max(Startdate) and max(Enddate) as expressions.