Utilization %

["... you mean these fans?"]

Hi,

I have a customer who rents storage units.

The customer want to see last yeas vs this years utilization where his total units are 87 ( 100 % ).

The only issue I am having is that each unit rented, the "End" date is empty.  The "End" date only populate when they "Move Out" a customer.

Any suggestions how to build a relationship to show each month to include the units that their ending is "IsEmpty" and if they "Moved Out" to include then in all previous months ?

It seems like the relationship I have tried to establish cancel out the data due to the "Move Out" date.

Here is the graph he would like to use.

Thanks 

[comment]

I don't think we have enough information here to suggest a solution. But:

28 minutes ago, Mr. Ignoramus said:

The only issue I am having is that each unit rented, the "End" date is empty.  

If that's your only issue, then use a calculation field =

If ( EndDate ; EndDate ; Date ( 12 ; 31 ; 4000 ) )

 

[LaRetta]

And be sure to uncheck in the bottom of the calculation box 'Do not evaluate if all referenced fields are empty.'

You could also use:

Min ( endDate ; Date ( 12 ; 31 ; 4000 ) )

Same thing ... be sure to uncheck that box.  

["... you mean these fans?"]

Hi,

Each unit is rented by month and the customer stays until they decide to cancel their agreement.  At this point the "End Date" is populated turing the Unit availability "GREEN" to the office staff.

The owner wants to see his utilization of units rented by month looking across "Last Yr vs This Yr".

If we populate the "End Date" then the unit goes green becoming available.

Currently this "End" date is the "Demarcation" for a lot of other stuff too.

So I am trying to build a relationship to take into consideration units rented each month ; do I build 24 relationships ? and then the units that become available need to be "NOT" considered.

I am not trying to be confusing, it is just that this "End Date" if empty the relationship fails with the units rented.  It throws off the calculation.

 

 

 

[comment]

8 hours ago, Mr. Ignoramus said:

If we populate the "End Date" then the unit goes green becoming available.

First, no one suggested populating the EndDate. The suggestion is to use a calculation field as the match field for your (undescribed!) relationship/s, instead of EndDate.

Also, I don't know what your screenshot is supposed to be showing, but even if you do populate the End Date with the date of December 31, 4000, nothing should turn green as the unit will not become available until that date - which in FileMaker is the end of time.

["... you mean these fans?"]

Hi,

Ok, I am just trying to provide information to solve my problem ... anyway can I start over ... ?

The customer would like to graph each month his units that are rented as a percentage of his overall units, currently he has 87 and he would like to see Last year vs this year in one graph.  I've posted the graph that the customer chose since it provide two columns and he would like these two columns to represent ea month ( last years and this years ).

All my relationship I have tried don't work. 

So I would like to ask the forum how they would do it and based on these idea I am hoping I can build something that I can bring back to the forum for further assistance if I have any issues since I will have actual reference point.

At this point, since all my attempt have failed, it is just total confusion now. Sorry.

 

[comment]

5 minutes ago, Mr. Ignoramus said:

anyway can I start over ... ?

Why don't you start by describing your current structure: what tables do you have and how are they related?

Also, please clarify what exactly should the chart show. For example, we are now in the month of June; should the chart always show the full year from January to December, including future months?

[comment]

FWIW, here's a stab at what I think you're trying to accomplish:

CompareYearsSlots.fp7

 

Note:

1. Partial overlaps are counted the same way as full overlaps;
2. Personally, I would prefer to get the chart's values by running a script, instead of through relationships.

 

Edited June 26, 2016 by comment

["... you mean these fans?"]

Hi Comment,

Looked at your file and I wish it was that simple, and maybe by the time this issue is resolve it will be this simple.

The customer would like to graph many table and we've establish his "DashBoard" layout on the "Home" table.  It carries only one record.

There will be many graphs on this Dash Board layout from many tables and the customer would like to start and graph his units rented vs not rented each month since we already have that data.  The customer is up to 111 units as his overall total now. 

The second table is the "Agreements_Units" table.  

When a customer rents a unit we enter all the data required and the customer "Start Date" is populated for that unit.  As each day goes buy they rent units and they also "End" units making them available for rent, so everything is kinda rolling.

Looking at the file that was attached, we have customer where their "End Date" is still open to this day. So I am thinking how to build something the graph can graph.  I need to include both each month across two date ranges; 20xx and 20xx all units rented including units that are actively rented through each month vs the over all total available.

So I am stuck how to establish a graph that incorporates both active unit each month where end date are "Empty" and end date are not empty "Ended".

I am thinking that maybe I establish a different "Match" field ?? 

The graph in your file is exactly what the customer is asking for visually.

I hope this helps.

Any suggestions I would be grateful.

Thank you.

["... you mean these fans?"]

Comment,

I looked at the file again you attached and noticed the "EndData_C" and the relationship graph with the grater than / less then operator for the Prev and Current Start / End date which I overlooked.

Based on those fields and the relationship I was able to implement my graph.

Thank you.