Formulae: Cumulative Figures and RUNNINGTOTAL example

windsymphonic · May 1, 2013 2:00AM

I have a set of data:

Company Name | Date | Daily Sales
ABC | 22/03/2013 | 22
ABC | 22/03/ 2013 | 23
ABC | 23/03/ 2013 | 40
ABC | 23/03/ 2013 | 50
DEF | 22/03/2013 | 10
DEF | 22/03/2013 | 20
DEF | 23/03/2013 | 30
DEF | 23/03/2013 | 60

In Omniscope, I have aggregated by Company Name & Date which gives me:

Company Name | Date | Daily Sales
ABC | 22/03/2013 | 45
ABC | 23/03/2013 | 90
DEF | 22/03/2013 | 30
DEF | 23/03/2013 | 90

My next step is to calculate cumulative sales by company name & date. So I want my outcome to be:

Company Name | Date | Daily Sales | Cumulative Sale
ABC | 22/03/2013 | 45 | 45
ABC | 23/03/2013 | 90 | 135
DEF | 22/03/2013 | 30 | 30
DEF | 23/03/2013 | 90 | 120

So how do I derive the cumulative sales?

Cheers, Firman

paola · May 1, 2013 5:10AM

You can try formula
RUNNINGTOTAL([Daily Sales],SUBSET2([Company Name],[Date]))

windsymphonic · May 1, 2013 7:36AM

Nope it doen't work..
For instance,

Company Name | Date | Daily Sales
ABC | 22/03/2013 | 22
ABC | 22/03/2013 | 30
ABC | 22/03/2013 | 0
ABC | 22/03/2013 | 0
ABC | 22/03/2013 | 0

ABC | 23/03/2013 | 10
ABC | 23/03/2013 | 15
ABC | 23/03/2013 | 0
ABC | 23/03/2013 | 0
ABC | 23/03/2013 | 0

After I aggregate the data, I get

Company Name | Date | Daily Sales
ABC | 22/03/2013 | 52
ABC | 23/03/2013 | 25
_____________________________________________
After I use the function you suggessted, I get

Company Name | Date | Daily Sales | Cumulative Sale
ABC | 22/03/2013 | 52 | 230
ABC | 23/03/2013 | 100

______________________________________________

When I look at the details, it shows:

Company Name | Date | Daily Sales | Cumulative Sale
ABC | 22/03/2013 | 22 | 22
ABC | 22/03/2013 | 30 | 52
ABC | 22/03/2013 | 0 | 52
ABC | 22/03/2013 | 0 | 52
ABC | 22/03/2013 | 0 | 52

Which gives the Cumulative Sate = 230

Thank you..

paola · May 2, 2013 1:51PM

If you wish to display Cumulative sale values for the Company/Date field combination to show how sale figure is growing through 'accumulation' (using RUNNINGTOTAL), there's little point in aggregating that field?

Welcome!

Categories

Ideas Parade

Tagged