Work around sorting running total

[bnsSteve]

Hi,

Just wondering if anyone has figured out a way to work around sorting a running total?

Here's an example of what I would like.
I have 2 groups
Group 1: CustomerID
Group 2: Date
Within group 2, there may be 1,2,5,10,etc dates.
I used a running total to count the number of dates in each CustomerID group. What I would like is to be able to sort this so it will give me the CustomerID with the most dates in it (ascending).

Hope this makes sense.

Thank you for any help at all!

 

[lbass]

Don't use a running total. Instead, place a date in the detail section and right click on it->insert summary-> distinctcount at the customer group level. Then go to report->group sort->all and select distinctcount of date->descending as your group sort field.

-LB

 

[bnsSteve]

Hi LB,

Thank you for your reply.

The part where you say place 'a date' in the detail section are you just referring to the 'date field' of the table I am using? I went through the steps using the date field that I brought into the detail section previously (which had been grouped by date), inserted the summary etc and it ran, however, the total I am getting for each Customer group all seem to be the same number in this case '5' (meaning it has counted that there are 5 different dates in each customer group). Not sure if I understood the date part.

Thanks again for your help.

 

[lbass]

You should be placing the date field in the detail section, right clicking on it, and inserting a summary->distinctcount on the datefield at the customer ID group level. And yes, this would return the number of distinct dates in each customer group. The point of this to just to activate the group sort. Not sure why they would all have the same count. Can you describe the steps you took?

-LB

 

[bnsSteve]

I believe I did just that, right clicked on date field --> inserted summary -> summarized date filed for distinct count -> summary location = customer ID group (group 1). Then ran the report. It appears to have a count of the dates in the report footer but all of the counts come back as 5, not sure why.

Thanks again for your help LB, I'll just have to leave it out if there's something wrong with my data.

 

[lbass]

When you insert the summary, it should appear in the customer group footer. Is this where you are seeing the 5's? I think you should check the detail level data and confirm whether this is correct. When you refer to count, can I assume you mean distinct count?

-LB

 

[bnsSteve]

Sorry about that, the summary does show up in the Group footer 1 (customer group) and yes distinctcount.

Thanks LB

 

[lbass]

Well, I don't think the distinctcount can be an error, so I think you should confirm the number of distinct dates within each group.

-LB