Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Refer to data below. I need to create the following column "Sales Last 2-Months" on a table. I don't want to use RangeSum(below(Sales,0,2)) as it doesn't return the desired results when SORTING the YearMonth column.
I've tried a different approach below, but this expression returns only the current month's total and not the desired current + previous sales total.
What am I missing on the expression below to make this rolling 2mt sum work ?
Sales Last To 2 Mt=
SUM( {$<YearMonth={">=$(=MONTHSTART(YearMonth),-2)<=$(=MONTHSTART(YearMonth),0)" }> }TotalAmount)
YearMonth | Sales | Sales Last 2-Months (?) |
2017-12 | 200,000 | - |
2018-01 | 300,000 | 500,000 |
2018-02 | 400,000 | 700,000 |
2018-03 | 500,000 | 900,000 |
2018-04 | 600,000 | 1,100,000 |
2018-05 | 700,000 | 1,300,000 |
2018-06 | 800,000 | 1,500,000 |
2018-07 | 900,000 | 1,700,000 |
2018-08 | 900,050 | 1,800,050 |
2018-09 | 900,150 | 1,800,200 |
2018-10 | 900,250 | 1,800,400 |
2018-11 | 900,350 | 1,800,600 |
2018-12 | 900,450 | 1,800,800 |
Sample Data:
Data:
LOAD
Dual(Year(YearMonth)&'-'&Num(Month(YearMonth),'00'), monthstart(YearMonth)) AS YearMonth,
TotalAmount
INLINE [
YearMonth, Sales
'12/01/2017',200000
'01/01/2018',300000
'02/01/2018',400000
'03/01/2018',500000
'04/01/2018',600000
'05/01/2018',700000
'06/01/2018',800000
'07/01/2018',900000
'08/01/2018',900050
'09/01/2018',900150
'10/01/2018',900250
'11/01/2018',900350
'12/01/2018',900450
];
I posted this a few weeks back and wanted to check if anyone has found a similar solution without using rangesum() or rangeavg(). Any feedback will be appreciated.
Try this app
expression:Sum(PSales)+SUm(TotalAmount)
LOAD
Dual(Year(YearMonth)&'-'&Num(Month(YearMonth),'00'), monthstart(YearMonth)) AS YearMonth,
Sales as TotalAmount,
Previous(Sales)as PSales;
Data:
load * INLINE [
YearMonth, Sales
'12/01/2017',200000
'01/01/2018',300000
'02/01/2018',400000
'03/01/2018',500000
'04/01/2018',600000
'05/01/2018',700000
'06/01/2018',800000
'07/01/2018',900000
'08/01/2018',900050
'09/01/2018',900150
'10/01/2018',900250
'11/01/2018',900350
'12/01/2018',900450
];
You may use Above function also.
There something strange with your expression which is causing you trouble.
SUM( {$<YearMonth={">=$(=MONTHSTART(YearMonth),-2)<=$(=MONTHSTART(YearMonth),0)" }> }TotalAmount)
You are using a 3 day interval when you want months.
Adjust it to
SUM( {$<YearMonth={">=$(=MONTHSTART(YearMonth,-2)<=$(=MONTHSTART(YearMonth)" }> }TotalAmount)
Yes this is the only way I got the calculation to work, well partially. How do you resolve the issue of when the report user sorts the date column in desc order ?