cancel
Showing results for 
Search instead for 
Did you mean: 

Distinct Count Running Total

Former Member
0 Kudos

<p>I have a report that gets a distinct count of items per day.</p><p>DistinctCount(, , "daily")</p><p> I would like to create a running total of the daily count for a weekly grand total.</p><p>Thanks!</p>

Accepted Solutions (1)

Accepted Solutions (1)

todd_hanna
Employee
Employee
0 Kudos

<p>This sounds like something that can be achieved in CR with little work. But it&#39;s hard to say without knowing anything about the structure of your report.</p><p>Can you group on the Date field so that your running total shows for each Day and then create a Report summary which is similar to the daily grouping summary, but for the report level? </p>

Former Member
0 Kudos

The report is grouped by date and the item count is DistinctCount(, , "Daily")</p><p>If the data was as follows:</p><p>1/1/07Â Â 9</p><p>1/2/07Â Â 10</p><p>1/3/07Â Â 12</p><p>1/4/07Â Â 15</p><p>1/5/07Â Â 20</p><p>Â The weekly total should be 66 and the weekly average should be 9 or 9.43.</p><p>Â However, I get and error if I try to use the following formula:</p><p>Sum(DistinctCount({ItemDate, , "daily))

 I would like to create a daily running total and calculate the weekly average based on the running total and the DayOfWeek.

 Thanks in advance for your help.

todd_hanna
Employee
Employee
0 Kudos

<p>I&#39;ll take a stab at this - So I understand that the report is grouped by Day and looks like this:</p><p>GF1: 1/1/07 9</p> <p>GF1: 1/2/07 10</p> <p>GF1: 1/3/07 12</p> <p>GF1: 1/4/07 15</p> <p>GF1: 1/5/07 20</p><p>&nbsp;</p><p>The first thing I&#39;d try is to create a another group on your Date field, this time selecting the grouping option to be "For Each Week". Take that group and move it so that it is Group1 and your current group is Group 2.... your structure will now look like this. </p><p>GH1: 1/1/07 < new &#39;week&#39; grouping&#39; > </p><p> GF2: 1/1/07 9</p> <p> GF2: 1/2/07 10</p> <p> GF2: 1/3/07 12</p> <p> GF2: 1/4/07 15</p> <p> GF2: 1/5/07 20</p><p> Then I would copy the Sum field (distinct count) that you are using in Group 2 and put it in the Group 1 footer. We could also create a formula to sum up all distinct counts per day, but a Distinct Count by week should accomplish what you are after. Then in a seperate formula, you could divide the DistinctCount of orders (by week) by the DistinctCount of dates (by week) to get your daily average....</p><p>formula:</p><p>DistinctCount ({Orders.Order ID}, {Orders.Order Date}, "weekly") <br />/ <br />DistinctCount ({Orders.Order Date}, {Orders.Order Date}, "weekly") </p><p>&nbsp;</p><p>Hope that helps. </p>

Former Member
0 Kudos

Thanks for your help. I was able to resolve the problem by creating running totals and then group.

Answers (0)