topic Re: count records that fall within given date range in QlikView

count records that fall within a given date range

b_garside — Thu, 10 Oct 2013 21:34:24 GMT

I have about 5700 opportunity records. Each has their own Create and Close dates.

Many overlap over months and years in active status overtime as one would expect.

The goal I have is to count them within a given date range. Such as Quarters, or months periods.

This will show a trend of how many were created within a given period.

I prefer not to use IntervalMatch due to the large table it generates.

I've tried InQuarter and other mathematical expressions. I don't believe I need set analysis but correct me if Im wrong.

Mstr_QuarterYear = is the Dim field used for my time spread.

CountOpps = is a simple 1 for each record so it can be Summed up.

This simply counts them and accumulates as it goes it seems.

Count( Distinct if( Mstr_QuarterYear>=(CreateDate) and Mstr_QuarterYear<=(CloseDate) ,CountOpps))

I have a Island Calendar I use to spread out along X axis. The problem having is I cant seem to take a group of records all having different time ranges and seeing if they fall within a time slot. Had no luck with the community searches.

Please help I plan to use the solution in many more charts.

Re: count records that fall within given date range

jagan — Thu, 10 Oct 2013 23:38:53 GMT

Hi,

If you want to use the Island Calendar Dimension in X axis then you need to join (link) the tables by specifying the common field in both the tables, then only you can use the Calendar Dimension.

Can you attach some sample data/file, it would be easier to understand.

Regards,

Jagan.

Re: count records that fall within given date range

b_garside — Fri, 11 Oct 2013 02:32:29 GMT

ok, makes sense I need to link the calendar with main table that contains the dates.

The question I have is what date do I link the calendar to; the Create or Close date? or something else?

This makes sense why nothing was working. I think my other issue is since each period has many overlapping dates how do I get my expression to evaluate them for so they get summed correctly?

It seems this has been done but could not find any clear examples as of yet.

Thanks

Re: count records that fall within given date range

b_garside — Fri, 11 Oct 2013 03:04:48 GMT

Do you think this post applies? It seems to match my scenario. Except I wont use a inline table.

http://community.qlik.com/message/234561#234561

Re: count records that fall within a given date range

swuehl — Fri, 11 Oct 2013 11:45:35 GMT

Brian,

it really would help us understand your issue if you could post a small sample app or some input data.

In general, you have the option to use a data island calendar or create links between your calendar and the fact intervals.

The first approach creates a slim data model, but if you want to analyze your data, you need to use quite complex expressions that show not so good performance and needs more chart memory than with the second approach.

To create a data model that somehow links your calendar to the intervals, Henric posted some excellent tutorials here and here.

Also these threads might be of interest:

Tutorial - Using Common Date Dimensions and Shared Calendars

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles

Re: count records that fall within a given date range

b_garside — Fri, 11 Oct 2013 12:48:35 GMT

Thanks Stephan

My data looks like this; The question I not sure is what date I use in my X axis from calendar, Create or Close? I'm leaning toward Create.

Id | createDate | CloseDate | CountOpps

11-01234 | 01.01.2011 | 31.12.2012 | 1

12-02345 | 01.05.2011 | 31.12.2012 | 1

13-03456 | 01.01.2013 | 31.12.2013 | 1

13-03455 | 01.05.2013 | 31.12.2013 | 1

Re: count records that fall within a given date range

swuehl — Fri, 11 Oct 2013 12:54:21 GMT

You can also transform your table from a cross table to a straight table like

CROSSTABLE (DateType, Date, 2) LOAD Id, CountOpps, createDate, CloseDate RESIDENT YourTable;

Your resulting table will have four fields, Id, CountOpps, DateType, Date.

Then you can link Date with your master calendar and use Date as dimension (or any field from the master calendar) and select the DateType in your expressions:

=sum({<DateType = {createDate} >} CountOpps)

=sum({<DateType = {CloseDate} >} CountOpps)

But it is all a little depending on what you want to analyze.

Re: count records that fall within a given date range

b_garside — Sat, 12 Oct 2013 21:58:39 GMT

Thanks Stephan

Yeah the only analysis I'm doing is detecting Overlapping date ranges per period such as Quarters which in effct are active opportunities over time.

Each opportunity has a date range many overlap so I'm counting how many overlap in that Quarter, or month depending on the chart.

Re: count records that fall within a given date range

b_garside — Sat, 12 Oct 2013 21:59:33 GMT

Im trying to use this post for my solution: http://community.qlik.com/message/4657#4657