Counting Records without Running Total

[diegoC11]

Hey all,

Can someone tell me how to count records that reset on a group without a running total?

The running total I have works well, but I can't pull the formula that includes it into a cross-tab to display averages.

The report has a Month grouping, and a week grouping. For this particular formula to display in cross tab, i need a formula that counts all the details within the month groupings.


Cheers,
DiegoC

 

[lbass]

Can you explain the summary you are trying to achieve? An average of what? At what level?

-LB

 

[diegoC11]

The report shows how many average days it takes for a workflow to go from open to closed status in our document management system. This is reported per week, but should also show monthly averages and quarterly averages.

I've managed to get the weekly averages so far.

I've done a grouping for Monthly workflows, then further grouped to weekly.



This is what I'd like the cross tab report to look like. I already have the weekly averages in a cross tab report. I may need to create separate cross tab reports that display the monthly averages and quarterly averages

Jan Feb Mar Etc
Week 1 3 2 2
Week 2 8 9 6
Week 3 4 6 3
Week 4 1 9 7
Monthly Average 4 6.5 4.5
Qtr Average 5


I have a formula for the days it takes for a specific workflow to go through the system, but the monthly totals/average formulas I have in the report I created with the help of running parameters. These formulas I can't bring into a cross tab report unfortunately, so I need a way of calculating these formulas without the help of running parameters.


Hope this clarifies and thanks lbass for taking a look

 

[lbass]

Are you currently using a crosstab for the weekly average? How did you calculate the weekly average? Please show the content of any formulas you used that contributes to the weekly average.

For your monthly and quarterly averages are you looking for the averages of the weeks (or averages of the months for quarters), or do you mean the averages of the raw data at those levels?

-LB

 

[diegoC11]

Thanks everybody,

I was able to pull this off. Attached is a link to the report definition if it helps anybody. A different grouping was made for weeks, months, and quarters.

Formula @workdays in the attached text was used for the subreport that showed weeks.

I created a formula that calculated workdays for each individual month. Called "Monthly Workdays".
I then used a running parameter averaging "Monthly Workdays" evaluating each record and resetting everytime it'd encounter a new monthly grouping. That running parameter was put in it's own subreport to show monthly averages.

The same logic above was used to create quarterly averages.


Lastly, if you look at the file, you'll see that workdays formula references "holidays" formula. That's just to ensure that holidays aren't accounted for in the calculation of turnover time for any particular workflow in our organization.

This might be too much info, but hopefully it can help somebody down the road. Cheers,Diego