4 Replies Latest reply on Dec 9, 2014 8:20 AM by Jerry Pudmanski

# dates

I have two questions on the attached worksheet.

1. on the first tab "months open" the formula months open does not seem to calculate correctly and I can't figure out why.   I am trying to take the difference between the open date and my parameter in months. In some cases the formula works but not in all cases.

2.  On the second tab i want to show a revenue per sq ft for locations open at least 18 months. This means each year the numbers of locations included in the calculation should change. I can't fugure a way to do this without having a different tab for each year.

Any ideas?

• ###### 1. Re: dates

Hi Jerry,

Months Open:

The first thing I see is that you have multiple records for each school. For example, School #655, if I set the parameter to be October 2014, Months Open should be 2. However School #655 has 3 records in your data source, 1 for every reporting period so you are going to see 6 (2 months * 3 rows of data). You can set Months Open to average (which would give you: (2+2+2)/3=2) but depending on what you are doing you may have to do a bit more. For this table it would work fine.

Open 18 Months:

You can create a calculation that says:

DATEDIFF('month', [Date Opened],[Reporting Period] )

This will tell you how many months a school has been opened up until the reporting period. (not up until the date you selected, so for each year it will change properly). From there you can just make it a boolean to check if the calculation is  greater than or equal to 18:

DATEDIFF('month', [Date Opened],[Reporting Period] ) >=18

(or a parameter of how many months you want!)

Check out the attachment to get some ideas!

Take care,

Keshia

• ###### 2. Re: dates

Hi Keshia,

Thanks for your help. You mentioned an attachment but here is none.

• ###### 3. Re: dates

Hmm, that's weird that you don't see the attachment... I copied the link it generated, maybe you can get to it that way?