Count instances between two fiscal dates

[jcampbell474]

I have store ID's in data from 201501 thru 201605, in one month increments.  E.g., 201501, 201502, 201503, etc...  All have an Open date (date store opened (in fiscal format (201501))) and some, the date they closed.

On a scale from 201501 - 201605, I need to tell how many stores were open, using the Open and Closed dates.  I don't want to put an IF in the load script as it will be an ongoing app...which would create ongoing maintenance.  I think I need a flag for each month that a store is open and another flag for the months it is closed.  I can't figure out how to do it.  Is this something that would be better coded in SQL, then into QV?  Does anyone have some advice to offer?  Other than the IF, is there a way to this?

[vikramv]

I Guess you first need to create a master calender using Opendate.

Creating A Master Calendar

Link this calendar using open date of your actual table.

Once done , it would be easy analyze next.

[swuehl · Accepted solution]

You should create reference dates (or YearMonth number) for all dates between Open and Close (replacing an unset Close value with e.g. today's date), then use this reference date as dimension and a simple

=Count(ID)

to show the open stores.

See for more detail

Intervalmatch Count Open Items

Creating Reference Dates for Intervals

The Master Calendar

Re: Autogenerate dates in a certain range

[jcampbell474]

Ok, I think I understand.  Thank you!  Something I forgot to mention in the 1st post, - the data (report) is laid out to have years in the rows and months in the columns.  I also have fiscal year and fiscal month fields for this reason.  Even if I generate the reference dates, using the year and month should still calculate the correct counts, right?

[swuehl]

You should create the reference dates for the lowest granularity needed to show open stores in your application.

Then link to this reference date (or month) field a master calendar, which allows to use all equal or higher granular fields for dimensions in charts or drill down, still giving the correct count.

[jcampbell474]

Thank you, it's working so far.  Doesn't look like reference dates are created for stores without a close date.  i.e., stores that are still open.  At the same time, it works perfect for stores with both an open and close date.  Am I missing something?

[swuehl]

As mentioned in my first reply, you should set the Close date to a value when creating the reference dates.

See the first thread I've linked for a discussion how you can do this.