Statistics data by week or day

[gilbenl]

Using this tutorial (http://www.anvildataworks.com/?p=554/) I have managed to produce a series of reports by month. These include basic counts and percents, nothing too fancy. Now, I would like to do the same thing, but display this same data by week or day. The end goal is to have a tabbed dashboard which would display running data by month/week/day, but will also allow specific months/weeks/days to be selected.

 

To achieve this for the months, I created a months table with 12 entries. Each has a DateStart and DateEnd, which are calculated by the following, respectively:

 

DateStart=MonthNumber & "/1/" & Year(Data_Dashboard::g_CurrentDate)

 

DateEnd=MonthNumber & "/" & Case(MonthNumber = 1 or MonthNumber = 3 or MonthNumber = 5 or MonthNumber = 7 or MonthNumber = 8 or MonthNumber = 10 or MonthNumber = 12 ; 31 ; MonthNumber = 2 and Mod(Year(Data_Dashboard::g_CurrentDate) ; 4) = 0 ; 29 ; MonthNumber = 2 ; 28 ; 30) & "/" & Year(Data_Dashboard::g_CurrentDate)

 

This produces a three column table with each month number, the first and last days of that month, which is then used in a series of calculations to give me the stats I need. My question is how to calculate dateStart/End by week with the first day being Monday and the last, Friday? I would also like to do this by day. 

[comment]

To achieve this for the months, I created a months table with 12 entries. Each has a DateStart and DateEnd, which are calculated by the following, respectively:

 

DateStart=MonthNumber & "/1/" & Year(Data_Dashboard::g_CurrentDate)

 

DateEnd=MonthNumber & "/" & Case(MonthNumber = 1 or MonthNumber = 3 or MonthNumber = 5 or MonthNumber = 7 or MonthNumber = 8 or MonthNumber = 10 or MonthNumber = 12 ; 31 ; MonthNumber = 2 and Mod(Year(Data_Dashboard::g_CurrentDate) ; 4) = 0 ; 29 ; MonthNumber = 2 ; 28 ; 30) & "/" & Year(Data_Dashboard::g_CurrentDate)

 

This is not a good method for several reasons (for example, it will break if you change your date format). To calculate the month's boundaries, try:

 

MonthStart = 

Date ( MonthNumber ; 1 ; Year ( Get ( CurrentDate ) ) )

 

MonthEnd =

Date ( MonthNumber + 1 ; 0 ; Year ( Get ( CurrentDate ) ) )

 

 

 

My question is how to calculate dateStart/End by week with the first day being Monday and the last, Friday?

 

That depends on where you start from. In general, the starting Monday of the week of your date can be calculated as =

YourDate - DayOfWeek ( YourDate - 1 ) + 1

[gilbenl]

Interesting...I thought the method for finding month boundaries seemed a bit too complex, but it made sense. The method you suggest also works and is much simpler, so it sticks. For my own education, could you explain how the calculation you suggest takes into account months with less than 31 days? Is this inherent in the Date( ) function?

 

 

As for the week, I'm a bit confused. I would like to create a report similar to the months but by week. I assume that would require a table with 52 entries, just as the month required a table with 12. It follows then for each of the 52 Week numbers, I would need a WeekStart= and a WeekEnd, correct? Wouldn't your suggestion only present the current week? See the attached image for an image of the table created for the months:

 

MonthsRecord.tiff

 

 

[comment]

For my own education, could you explain how the calculation you suggest takes into account months with less than 31 days? Is this inherent in the Date( ) function?

 

The Date() function is smart enough to figure out that the zeroth day of a month is actually the last day of the previous month.

 

 

 

I would like to create a report similar to the months but by week. I assume that would require a table with 52 entries, just as the month required a table with 12.

 

it is slightly more complicated with weeks, because - IIUC - you want to start by selecting a week number, and computing the dates from a week number and year is not exactly trivial - see:

http://www.briandunning.com/cf/1439

http://www.briandunning.com/cf/229

[gilbenl]

Outstanding! Thank you, sir!

 

The website is also a phenomenal resource as well.

 

Just to help out the next guy who comes through looking for this, the actual function I put in is:

 

c_WeekStart=Let ( nY = Date ( 1 ; 1 ; Year(Get(CurrentDate))) ; nY - DayOfWeek ( nY ) + 7 * WeekNumber - 5 )

 

c_WeekEnd=Let ( nY = Date ( 1 ; 1 ; Year(Get(CurrentDate))) ; nY - DayOfWeek ( nY ) + 7 * WeekNumber - 1 )

 

Where WeekNumber is a auto assign number field 1 to 52. Output is the Monday and Friday of each week, respectively. Credit for the function and additional resources can be found here: http://www.briandunning.com/cf/1439

[comment]

WeekNumber is a auto assign number field 1 to 52.

 

That might not be enough for some years, for example 2000.

[gilbenl]

I went ahead and tested it by extending it through 100 rows. Seems to respond appropriately, but will keep an eye out going forward. Thanks to your help, I was able to make considerable progress on the DB and it's beginning to come together. Unfortunately, there are two elephants remaining:

 

First is how to effectively quantify the delays. For each record, the observer may enter up to 5 individual delay "codes" in the fields labeled delay 1-5, with one delay per field. Delays are entered by selecting the desired "code" from a drop-down list populated by custom values. The custom values are designated as number fields and formatted as follows: ## Delay Reason. (eg: 11 Outpatient Late Arrival). The first digit indicates the group, and the second digit specifies the exact reason within that group. So, if the leading digit is 1, this indicates that the delay is patient related. 2 is physician related, and so on up to group 6. As I mentioned, the observer may enter up to 5 individual delay codes that are mutually exclusive. They are instructed not to enter duplicates, but there is no measure to prevent it. The result looks like this:

 

DelaysExample.tiff

 

With these delays, I need to quantify them a couple ways. First, I need to count the number of appearances of each delay. I can easily do a summary for each delay field individually (IE: Delay 1) and find out a percent total. My question is how to combine Delay 1-5 and output a unified count without regard to which delay field identical codes are in. For example, if in record #1, Delay 2 is "11 Outpatient Late Arrival," but in record #50 the same code appears in Delay 4, how would I be able to count those?

 

Then it gets a bit more tricky: Recall how the leading digit designates a delay "group." I would like to then assign a weighted responsibility based on group. For example, if Record #1 had only one delay from Group 2-Physician Related, I want FM to put a "responsibility score" of 100 on "Physician" for that record. But, let's say Record #4 had 2 delays: One from Group 1 and one from Group 2. I would then like FM to divide the score between the two groups, 50/50. To complicate matters, there are records where you might have 2+ delays from one group and 1 delay from another. It would then need to assign 66/33.

 

I have attempted a few things to achieve this, but have had no notable success. I have attempted to do a summary for each delay, and a sub-summary for the summaries from all 5 groups. I have also attempted to create a secondary table that combines all 5 delays into 1, which obviously didn't work either. So, I'm pretty much stumped on this one.

 

 

 

My second issue is MUCH simpler. I have created a dashboard to display charts of the on time percent (OTP), which looks like this:

DashExample.tiff

 

You will notice that there is a drop down menu on the left of the portal. This menu contains all values from the Field "WeekStart," which corresponds to the X-axis of the graph. Although not shown, there is a second drop down menu that contains values from the Field "WeekEnd." Using these two drop downs, I would like to have the ability to select the range of data being displayed on the graph, as well as the results of the calculations displayed in the fields to the right. To do this, I have attempted a couple of approaches. For one, I tried to setup the button to do a simple find. Second, I attempted to do a constrain. Neither worked. I know there is a simple way to do this...

 

 

I wish I were able to post the FM file itself, but it is being used as a secondary method while I'm building it. So, HIPAA etc etc etc. I have tried to be as clear as possible, but I recognize that this may be a bit confusing. If anyone is willing to take a swing, I'll be around to clarify when needed.

 

Thanks again guys. This DB has grown by leaps and bounds thanks to your help.

[comment]

Anytime you find yourself numbering fields like Delay1, Delay2, etc. you are most likely having a structural issue. You should use a one-to-many relationship instead, where each delay is a record in a child table. Then it's easy to summarize the delays in a report produced from their own table.

 

I am not sure I understand your second issue. It seems like this too should be solved by a relationship, limiting the number of week records. Then point your chart to use data from the related records.

[gilbenl]

Anytime you find yourself numbering fields like Delay1, Delay2, etc. you are most likely having a structural issue. You should use a one-to-many relationship instead, where each delay is a record in a child table. Then it's easy to summarize the delays in a report produced from their own table.

 

I am not sure I understand your second issue. It seems like this too should be solved by a relationship, limiting the number of week records. Then point your chart to use data from the related records.

 

 

Edit: May have figured out the root of the issues. 

 

Edit 2: Definitely figured it out...relationships click, portals are neat, mind blown...woo!

 

Relationships1.tiff

 

 

So now I have an empty database with all the same fields, but correct relationships, and a full data base with inappropriate formatting. Is there any way to transfer entire fields between tables, or will I have to create an entirely new DB and custom import the old one, or manually enter all 150 existing records?

[comment]

You could perform a series of imports, each time importing the parent ID and one of the numbered fields.

[gilbenl]

I've made great progress in my DB thanks to the help I've received thus far, but now I've hit another wall...so, I'm back. For each record, you can have 1-5 delays, as seen here: 

 

Delays.tiff

 

You will notice that the delay "codes" are a pair of numbers, with a text description. The field is set to number, FYI. The first digit denotes to which group that delay belongs to. For example, delay #11 is in Group 1 (patient related delays). As I described earlier, some cases have 3 delays, each from a different group, where as some have 3 delays, of which multiple belong to the same group. What I need to do is find an effective way to have FM assign a weighted score to each delay in each case, and then produce a percent total. The long way of doing this can be seen in this excel file:

 

https://dl.dropbox.com/u/1149002/DelayDataExample.xlsx

 

So, for example, for ID_Delay 9, group 9 was 100% responsible. Similarly for ID_Delay 11, there were 2 codes for group 2 and one for group 3 so 66,66,33 was assigned. I need to find a way to make FM do the same thing that was done in that excel file, only automatically.

 

Any ideas?