Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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
For months you can use this expression:
month(date(date#(YearMonth,'YYYYMMM'),'YYYYMMM')) as months
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
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.
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
See also
Calculating rolling n-period totals, averages or other aggregations
Click on Sort Tab...
And check Expression and use YearMonth field in ascending order.
Hope it helps you!!
Regards,
Kavita
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