Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I would like to have the same result that I have in the table:
So the total acc. sum should still be 214. But I would like to see only 12 last months.
So expected results would be (but without slider of course):
Is there any method to get that result? So anything before 01/02/2014 should be added to 01/02/2014.
Raw example data:
load * inline [
Date, Value
01/01/2014, 10
01/02/2014, 20
01/03/2014, 10
01/04/2014, 24
01/05/2014, 20
01/06/2014, 12
01/07/2014, 15
01/08/2014, 11
01/09/2014, 9
01/10/2014, 19
01/11/2014, 20
01/12/2014, 11
01/01/2015, 21
01/02/2015, 12
];
THANK YOU
and if you want to show 01/02/2014 also..
then
dimension
=Aggr(IF(Date >= AddMonths(Max(Total {1}Date),-12),Date),Date)
expression
RangeSum(Above(IF(Date < AddMonths(Max(Total {1}Date),-11),SUM(TOTAL {<Date = {"<$(=AddMonths(Max(Total {1}Date),-11))"}>}Value),SUM(Value)),0,RowNo()))
Not sure but I think for this accumulation should happen in the script -
Table1:
load * inline [
Date, Value
01/01/2014, 10
01/02/2014, 20
01/03/2014, 10
01/04/2014, 24
01/05/2014, 20
01/06/2014, 12
01/07/2014, 15
01/08/2014, 11
01/09/2014, 9
01/10/2014, 19
01/11/2014, 20
01/12/2014, 11
01/01/2015, 21
01/02/2015, 12
];
Table2:
load *,
if(Recno()=1,Value,Rangesum(Value,peek(AccSum))) as AccSum
Resident Table1;
Drop table Table1;
Expression used as - Sum({<Date={">=$(=Addmonths(Max(Date),-12))"}>}AccSum)
that's great, but is there any way to do it only in the front-end modifying expr without adding new fields?
Create a Straight Table
Dimension
=Aggr(IF(Date > AddMonths(Max(Total {1}Date),-12),Date),Date)
Expression
RangeSum(Above(IF(Date <= AddMonths(Max(Total {1}Date),-11),SUM(TOTAL {<Date = {"<=$(=AddMonths(Max(Total {1}Date),-11))"}>}Value),SUM(Value)),0,RowNo()))
and if you want to show 01/02/2014 also..
then
dimension
=Aggr(IF(Date >= AddMonths(Max(Total {1}Date),-12),Date),Date)
expression
RangeSum(Above(IF(Date < AddMonths(Max(Total {1}Date),-11),SUM(TOTAL {<Date = {"<$(=AddMonths(Max(Total {1}Date),-11))"}>}Value),SUM(Value)),0,RowNo()))
Good one!
You can also try this
Dimension
Date
Expression
=RangeSum(Above(Sum({1}Value), 0, RowNo())) * Avg({<Date = {"$(='>=' & Date(Monthstart(Max(Date), -12)) & '<=' & Date(Max(Date)))"}>}1)
Excellent !