My ultimate goal is to calculate the percent of items out on, say, the 15th of each month, for the previous 12 months. First, I will share steps taken to get the numerator (items out), then I will share my plan for the denominator (total number of items).
I used the items out table in polaris to provide an initial value for ‘items out’. I then used Transactions data to find the number of checkouts (original checkouts only) and checkins. In using this data to calculate a running total of items out by month, I seem to be making an error. Is ItemsOut a calculation I could more easily derive with Transactions Data alone? Or, in Polaris, how is the ItemsOut table returning the result set? I am hoping the answer to this question will provide a template to apply to the denomintor (total number of items), where I would start with the CircItemRecords table, total number of items, as an initial value, and count back with PolarisTransactions [ItemRecords created - ItemRecords deleted]. Is this approach tenable? Thanks for any help with this.
Could you restate your goal? Are you looking to only track this number moving forward? Or do you want to mine the Transactions database to provide historical information?
Yes, I want to mine the Transactions database to provide historical information. I wanted to produce a requested report, but at the moment it would be only one bar graph from a single ItemsOut capture on July 31st! I had hoped to ‘mine’ back one fiscal year to provide both a nicer view of what the report (in Tableau) would look like and also a good historical context for comparisons in the upcoming year.
The end user has requested a report to state the percentage of the total (physical) collection that is lent out at a specified time, (we’ve decided the last day of each month) ( and here ‘lent out’ is meaning the item is anywhere in its loan period between checkout and checkin). So, my hope was to simply ‘count back’ from the Polaris ItemsOut table initial value. Checking in with a colleague, we became unsure of our results. I’ll provide any more information and really appreciate your help here but I did not want to make things more confusing up front. Have you ‘counted back’ any transactions using an initial value from Polaris database and subsequent values from PolarisTransactions? If I can understand this example, I will try to duplicate the same framework for counting back (total # of items) in CircItemRecords with Item Record Created and ItemRecordDeleted values from PolarisTransactions. Thanks again and I hope I am not too off base with this??
Although I don’t think I’ve ever used these, but this might be a candidate for the [Aggregates] tables in the Transactions Database. Have you ever used those tables @CyberpunkLibrarian or @mhammermeister?
Does anyone know if SimplyReport exposes those in some sort of sane way?
@wesochuck
I have explored the Aggregates tables a bit but forgotten about this. The screenshots show the data table joins in Tableau and a simple check in/check out bar chart. I will look more closely at the values, I wonder if electronic items can be removed? Thanks for mentioning this table!
Yeah, in looking a bit more at these, I think there is still going to be an issue answering the question “how many items did we have on the shelves on this day in the past”?
I don’t think that information is tracked anywhere, so it is going to require a lot of calculating. Looking at the creation date of items and then combing the transactionsdetails for all the deleted items from the current date.
This is one of those things where I’d hope you have full access to your database so you can create custom tables and, perhaps, even a secondary database to hold on to information used for stats like this. Because if I wanted to get something like this, I’d have a custom table that scoops up the total number of records in ItemCheckouts every night. Then I’d reference that to get your ratio as you could use CircItemRecords to pull a count of items with a FirstAvailableDate before X date.
Mining back before that in the TransactionsDatabase, as @wesochuck said, is going to be tricky at best. You’d wind up looking for 6001 transactions and then trying to find their corresponding 6002 transactions.
