topic show values between dates in QlikView

show values between dates

Fri, 05 Jun 2009 04:29:51 GMT

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

show values between dates

johnw — Fri, 05 Jun 2009 04:45:55 GMT

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

show values between dates

Fri, 05 Jun 2009 05:05:37 GMT

Correct syntax is as below

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

Regards,

Amit

show values between dates

Fri, 05 Jun 2009 05:10:50 GMT

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

show values between dates

johnw — Sat, 06 Jun 2009 00:01:41 GMT

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)

show values between dates

Sat, 06 Jun 2009 05:15:30 GMT

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.

show values between dates

johnw — Sat, 06 Jun 2009 05:43:18 GMT

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?

show values between dates

Sat, 06 Jun 2009 16:15:36 GMT

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

show values between dates

Sat, 06 Jun 2009 17:27:36 GMT

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!

show values between dates

johnw — Tue, 09 Jun 2009 01:21:53 GMT

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.

show values between dates

Tue, 09 Jun 2009 14:57:50 GMT

Thanks for this excellent solution John! This is exaclty where I was looking for!

show values between dates

biester — Mon, 15 Jun 2009 18:09:37 GMT

Hi !

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

HTH,

Martin