Skip to Content
0
May 25, 2018 at 10:53 AM

Calculating running count based on sorted data

459 Views

Hi Experts

I have a table that has two columns . A dimension and a Measure. I have the table sorted on the Measure in Descending order so that the table looks like this. Code:
City Cases
C6 10
C2 8
C5 7
C4 3
C1 2
C3 1

I created the following variable Code:
[Running Count] = RunningCount([Cases])

When I add this column to my table the result is this: Code:
City Cases Running Count
C6 10 6
C2 8 2
C5 7 5
C4 3 4
C1 2 1
C3 1 3

Basically it is doing the running count with the data sorted on City and ignoring my descending sort on cases.

The Business Objects help file has this statement.

If you apply a sort on the measure referenced by the RunningCount() function, Web Intelligence applies the sort to the measure first, then calculates the running count.

So I am thinking my result should be this: Code:
City Cases Running Count
C6 10 1
C2 8 2
C5 7 3
C4 3 4
C1 2 5
C3 1 6

The reason for the runningcount variable is I want to limit the table to only show the first 35 rows but since the runningcount isn't applied correclty this will not work.

Any ideas?