Cumulative sum for last 12 months

[grajmca_sgp123]

Hi I am trying to do the cumulative sum for last 12 months ,I have used below expression for cumulative sum but my yearmonth did nor sorted properly.

How can we sort it

YearMonth	sum( aggr( rangesum( above( sum([va]),0,12)),YearMonth))
2014Oct	169591262.31912
2014Nov	195147123.40447
2014Dec	183528473.22945
2015Jan	193411687.76049
2015Feb	10023235.79498
2015Mar	-5481521.8884398
2015Apr	12031879.59047
2015May	22385354.41466
2015Jun	7010994.9133703
2015Aug	33994469.05486
2015Jul	29853128.87857
2014Sep	190288699.13375
2014Jun	183123809.9909
2014Jul	199262258.59527
2014Aug	181742724.37857

[jonathandienst]

Change YearMonth into a proper date field during load, and then sort it numerically...

     LOAD

          YearMonth As YearMonthText,

          Date(MonthStart(Date#(YearMonth, 'YYYYMMM')), 'YYYYMM') As YearMonth,

Now use YearMonth as the dimension and sort numerically.

[grajmca_sgp123]

how about if we use dim as Months only..Oct,Nov,Dec,Jan,Feb.Mar

and above months are defining from  Date field (format of date ::MM/DD/YYYY)

so how can we use Months as dim for cumulative sum for last 12 months

[ankitbisht01]

For months you can use this  expression:

month(date(date#(YearMonth,'YYYYMMM'),'YYYYMMM')) as months

[sasiparupudi1]

You need to have year value in order to get the cumulative sum for  the last 12 months. I would go with Jonathan's suggestion.

hth

Sasi

[jonathandienst]

You can add year and month dimensions easily enough:

     LOAD

          YearMonth As YearMonthText,

          Date(MonthStart(Date#(YearMonth, 'YYYYMMM')), 'YYYYMM') As YearMonth,

          Month(Date#(YearMonth, 'YYYYMMM')) As Month,

          Year(Date#(YearMonth, 'YYYYMMM')) As Year,

          ...

Just keep in mind that Month and Year are good for YTD calculations and selections but are not appropriate for a rolling 12 month analysis.

[jonathandienst]

You could also add a 12 month flag -

     LOAD

          YearMonth As YearMonthText,

          Date(MonthStart(Date#(YearMonth, 'YYYYMMM')), 'YYYYMM') As YearMonth,

          Month(Date#(YearMonth, 'YYYYMMM')) As Month,

          Year(Date#(YearMonth, 'YYYYMMM')) As Year,

          If(Date#(YearMonth, 'YYYYMMM') > AddYears(Today(), -1), 1, 0) As Flag12Months,

          ...

Then use the flag in set expressions like:

     Sum({<Flag12Months = {1}>} Amount)

with a dimension of YearMonth

[jonathandienst]

See also

Calculating rolling n-period totals, averages or other aggregations

[kavita25]

Click on Sort Tab...

And check Expression and use YearMonth field in ascending order.

Hope it helps you!!

Regards,

Kavita

[Anonymous]

Hi Rajesh,

Try below expression in Sort Expression and select ascending in dropdown

=Left(YearMonth,4) & '' &match(Right(YearMonth,3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

Regards

Neetha