Cumulative percentage

building reports webintelligence
1

I have a table in which i determine the percentage orders per working day.
I use the formula “PercentageOrders” =Percentage([NumberOf_Orders];Col) notation #,##0.0[%]%.

Now i want the cumulative percentage of these percentages.

I use the formula "=RunningSum([PercentageOrders]) notation also #,##0.0[%]%

But then i get strange results last NumberOf_Orders cumulative percentage = 299,3%

i just want to sum the percentages

1% 1%
5% 6%
10% 16%
4% 20%
etc.
etc.

What am i doing wrong?

Thanks

Pedro

PedroJ (BOB member since 2010-06-11)

2

what’s the logic behind the percentage object? you may need to do a running sum on the object used to get the percentage, rather than the percentage itself

erik.stenson :us: (BOB member since 2012-07-30)

3

I’ve never used that percentage formula

Percentage([NumberOf_Orders];Col)

But what i would have done instead is

 RunningSum([NumberOfOrders])/ (Sum[NumberOfOrders] In Block)

Should then basically take the number of orders cumulative at each row and divide by the total number in the block. (Can swap block for table/report/section etc… or even use in () etc…)

JPetlev (BOB member since 2006-11-01)

4

When i use:

RunningSum([NumberOfOrders])/ (Sum[NumberOfOrders] In Block)

i get strange results, for the percentage.

Still i want to have the Cumulative percentage. The logic is that i want to now the total percentage that is returned after … days.

I added a excelfile to explain my question.

Thanks

Pedro
Test_RunTime.xls (26.0 KB)

PedroJ (BOB member since 2010-06-11)

5

Hi

Simply use this this function

=RunningSum(Percentage([Orders];Col)) … it will work successfully.

Regards
Suresh

suresher83 (BOB member since 2010-10-25)

6

=RunningSum(Percentage([Orders];Col)) gives the same result as the variable that i used: =RunningSum([Percentage])

(see the results is the attachment)

I don’t get it why the result at the end is 299,35% with bothe codes.

Who can help me?

Thanks Pedro
Test_RunTime2.xls (27.0 KB)

PedroJ (BOB member since 2010-06-11)

7 
RunningSum([NumberOfOrders])/ (Sum[NumberOfOrders] In Block)

Should work just fine… do me a favor…
If you break out the two sides of the formula the running sum of orders, vs the total in the block into two columns… which one is incorrect?

I’m wondering if perhaps there is some more data in your data provider that your not showing, which is causing the difference in calculations.

What is interesting is i can see the formula IS in fact adding up to 100% (well 99.8 which makes me think there is hidden data somewhere), it’s just not in the breakout you are expecting.

JPetlev (BOB member since 2006-11-01)

8

When i broke up the formula in the two blocks i saw what was wrong.

This report is divided into years, and somehow the RunningSum and Sum is over the total of the years, which are presented. When i use only one year its ok, Two years the totals per year are summed.
I used “In Section” “In Break”, same result.
I added as attachment the whole table now with the extra years and above what the formula’s are. This can explain something?

Hope someone can help me.

Thanks

Pedro
Test_RunTime3.xls (28.0 KB)

PedroJ (BOB member since 2010-06-11)

9

I haven’t looked at the attachment… but is sounds like all you would need to do is

RunningSum([NumberOfOrders]) IN ([Year]) / (Sm(NumberOfOrders) In ([Year]))

Basically use context operators to tell the summation what to sum and when it should reset itself. I know Sum can use the ‘in’ operator, not sure about RunningSum off the top of my head… help within WEBI will show you for sure.

JPetlev (BOB member since 2006-11-01)

10

I tried your statement and i get in the first year at all values 100% and the second year 200%.

I also tried several statements with [year] in it, but no result.

What is going wrong. I can’t get it?

Pedro

PedroJ (BOB member since 2010-06-11)

11

Sounds like you need to use “Year” as a reset Dimension in your Runningsum:

=RunningSum([NumberOfOrders];([Year]))

HTH

NMG

mcnelson :uk: (BOB member since 2008-10-09)

12

Hello NMG,

the Year as reset Dimension in the RunningSum did the trick.

Thanks
Pedro

PedroJ (BOB member since 2010-06-11)