smcnulty2000
Technical User
Crystal 10. MS-SQL Server.
My problem:
This is a topN problem. But it is a little more weird than most.
I have a set of receipts in a table, and another with the entity related to them.
I have a report built that gets the top 200 payers out of a list of about 1650 customers, Based on the full month. I'm using group sort for this. I'm also able to get the rank on these numerically using this:
EvaluateAfter (Sum ({@04AmntPdThisMonth}, {@Groups01License}));
global numbervar numGreybar;
numGreybar:=numGreybar+1.0;
Problem: I have two more columns to complete. One shows the same customer's numerical rank at YTD. The last shows the customer's numerical rank at YTD Last year at this date.
So essentially I have three different rank columns, each of which has to rank the data set according to their own totals.
So the output might look like this:
I'm fairly certain that if I had to do only 100 of these, I'd be done by now because I'd use TopN. But TopN only works for 1-100 inclusive.
If anyone has a clever way to get TopN for 200 (for multiple columns), or another approach to this I'd be happy to hear it.
One thing I did try was dropping the data into an array and then using a for loop to count. However this didn't quite work because of an evaluation time issue.
As usual, if I've given insufficient information let me know and I'll update.
Thanks,
Scott.
My problem:
This is a topN problem. But it is a little more weird than most.
I have a set of receipts in a table, and another with the entity related to them.
I have a report built that gets the top 200 payers out of a list of about 1650 customers, Based on the full month. I'm using group sort for this. I'm also able to get the rank on these numerically using this:
EvaluateAfter (Sum ({@04AmntPdThisMonth}, {@Groups01License}));
global numbervar numGreybar;
numGreybar:=numGreybar+1.0;
Problem: I have two more columns to complete. One shows the same customer's numerical rank at YTD. The last shows the customer's numerical rank at YTD Last year at this date.
So essentially I have three different rank columns, each of which has to rank the data set according to their own totals.
So the output might look like this:
Code:
xxxxxxxxxx R1 R2 R3
customer 1 1 2 1
customer 2 2 1 2
customer 3 3 3 4
customer 4 4 4 3
customer 5 5 5 5
If anyone has a clever way to get TopN for 200 (for multiple columns), or another approach to this I'd be happy to hear it.
One thing I did try was dropping the data into an array and then using a for loop to count. However this didn't quite work because of an evaluation time issue.
As usual, if I've given insufficient information let me know and I'll update.
Thanks,
Scott.