Cumulative Sum of Data in a chart

[#1]

Hi,

I have got a table with data as below

Month-Yr	Sales
Jan-11	1000
Feb-11	120
Mar-11	1350
Apr-11	17500
May-11	18000
June-11	24000
Jul-11	22000

Now I need to create a chart which shows cumulative sum of sales only from Apr-2011 to Jul-11. However The Apr 2011 sales should be a cumulative sum of sales from Jan-11 thro' to April'11

Is this possible? Please help

[Sokkorn]

Hi,

Use this

[Data]:
LOAD * INLINE [
Month-Yr,    Sales
Jan-11,    1000
Feb-11,    120
Mar-11,    1350
Apr-11,    17500
May-11,    18000
June-11,    24000
Jul-11,    22000];

Create a bar chart

1. Month-Yr     as Dimension

2. SUM(Sales)     as Expression

3. Sort by Expression (Sort Tab) : Match(LEFT([Month-Yr],3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39)))

4. In Expression Tab : Full Accumulation

See the sample attached file.

Hope this help

Regards,

Sokkorn Cheav

[#3]

Thanks for your reply Sokkorn,

However, I only need to display cumulative data from Apr-11 onwards, not from Jan-11.

Is there a way I can do this?

[Sokkorn]

Hi,

Let do this way:

1. Create one variable : Settings --> Variable Overview --> Add

     a. Variable Name : vPrevious

     b. Definition : =SUM(IF(Match(LEFT([Month-Yr],3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39)))<4,Sales))

2. Create Bar Chart

     a. Add Calculated Dimension : =IF(Match(LEFT([Month-Yr],3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39)))>=4,[Month-Yr])

     b. Check on Suppress When Value Is Null

     c. Expression : =IF(Match(LEFT([Month-Yr],3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39)))=4,SUM(Sales) + vPrevious,SUM(Sales))

     d. Check Full Accumulation

See the sample attached file also.

Hope this help.

Regards,

Sokkorn Cheav

[rajni_batra]

hello, Can u plz explain how does this expression works.

: Match(LEFT([Month-Yr],3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39)))

[Sokkorn]

Hi Rajni,

This expression is used to convert from month name to month number. Let say Jan (3 Char) then it will convert to 1. I use this for sort order in chart. If in the source table have month number already then we don't need this expression. Depend on data in app above is text (Jan-11) so I use (LEFT([Month-Yr],3) to get only Jan. The end the purpose of this expression is convert month name to corresponding number.

Regards,

Sokkorn Cheav

[rajni_batra]

Ok.. thnk.

Sokkorn

[Sokkorn]

Welcome Rajni

[#9]

Thanks Sokkorn. Is there a way I can make this dynamic? That is, select which month to start accumulating from rather than from April only.