How can I compare the columns of this Provider-Encounters Summary Table?

Question

I have created a summary table of providers and the number of encounters (routine short patient visits). This table is setup weekly for the past 14 weeks. What I want to do is to compare the last few weeks to the current week & take an average and put in a new field, such as 3 Month Moving Average. I know the formula previous two months encounters + current month encounters and all divided by three.

What I'm trying to do is to get a formula to add the three months of encounters for each provider. But the data is setup by grouping and crosstabs, so their aren't actual field data, since the data is crunched by the grouping & crosstabs.

I am thinking doing a query is the way to go? The Encounter date is updated by BOX at regular intervals or manually. The provider and encounter date with other fields are added as one record. So if Provider Jeff Smith has 7 encounters today, then Sep 9, 2022 shows up 7 times with the provider name Jeff Smith.

------------------------------
Bob T.
------------------------------

blakeharrison · Answer

Query formulas would certainly be one way to accomplish this. You could also do regular Summary fields. For each timeframe you want to capture - Current Week, Prev Week, 2 Weeks Ago, etc - you'll want to build a Summary field using the "Number of Encounters" option and then setting a filter at the bottom, much like you would in a report (see the image below).

Admittedly, the date filters aren't always the best when you want to target a full week (Sun to Sat), but in those instances, I build out Formula Checkbox fields on the table I want to summarize. These formulas would be used as the filter then. So, essentially, you would have a checkbox for each timeframe and then use the checkbox in the Mating Criteria.

------------------------------
Blake Harrison
bharrison@datablender.io
/
------------------------------

bobthomas1 · Answer

Thanks Blake,I'm going to print your response out. I didn't get to work on today, was outside on my John Deere Zero-Turn that caught fire six weeks ago, almost done when I got sick on Friday. So now I just started it and five acres of mowing just got easier for me and my time.Warm regards,Bob T.

bobthomas1 · Answer

I replied to Mark too, via the email option, but apparently not being added to my thread. Just wondering. But thanks to both Mark & Blake. Tomorrow I should be almost 100% and on QB. Today I was outside working when it got warm.

------------------------------
Bob T.
------------------------------

bobthomas1 · Answer

Okay, I thought maybe putting a simpler chart up. This is the start of a list of providers &amp; total encounters for the past 14 weeks, set in the filter section.&nbsp;I took out the grouping to just get a total. The total number would be the one that I want to delve into by week or if by day, then it's 98 days of data (14 weeks)I have added Lookup Fields and/or Summary Fields, but getting zero numbers or totals. I keep learning about the use of the summary, lookup &amp; query operations. I'm thinking this will work out beautifully once I figure this out.&nbsp;In the meantime I will search for more in Community, YouTube even QB Junkies, though I can only access their free lessons snippets.------------------------------Bob T.------------------------------

bobthomas1 · Answer

I just grouped by 'Weekdays Only Filter' (formula-checkbox) and was able to get a M-F number of encounters and weekend number. So I created three new formula-checkboxes with current week, last week &amp; two-works-ago fields.&nbsp;I need to use the date field 'cln enc date-date' field (already created) to specify a week period for each. I will read the date commands and try.------------------------------Bob T.------------------------------

Forum Discussion

How can I compare the columns of this Provider-Encounters Summary Table?