Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have one requirement. In my dashboard I have 1 Dimension Date and 1 measure Values.
Now My Requirement is I want to sum last 30 days data for every date. For example if It is 1 Nov 2015 then it will show sum of 1 Oct 2015 to 31 Oct 2015. if 2nov 2015 then from 2 oct 2015 till 1 nov2015.
Sample File is attached.
use accumulation sum for that.
RangeSum(Above(TOTAL Sum(Measure), 0, RowNo(TOTAL)))/sum(total Measure)
Thanks
suresh
Consider using an As Of table:
HI,
try some thing like this
Load Date,
Date(Date#(Date,'YYYYMMDD'),'MM/DD/YYYY') as New_Date,
Values from Table;
Expression:
=Sum({<Date={">=$(=Date(AddMonths(max(New_Date),-1),'MM/DD/YYYY'))<=$(= Date(max(New_Date)-1),'MM/DD/YYYY')"}>}Value)
You can't easily do that with your current data model. Firstly, your date field is not in a proper date format, so you cannot use normal date functions and date arithmetic to calculate "date - 30 days". Secondly, you will need a date island to use dates as both a dimension and as a row-specific filter.
1. Date functions - load the date using
Month:
LOAD Date#(Date, 'YYYYMMDD') As Date,
Value
FROM
C:\Users\Desktop\Sheet.xls
(biff, embedded labels, table is Sheet1$);
2. Create a date island. This is a table containing duplicate values of the dates, but not connected in any way with the rest of the data mode.
DateIsland:
LOAD Distinct Date As IsDate
Resident Month;
Now you can use IsDate as the table dimension, and use the expression:
Sum(If(Date > (IsDate - 30) And Date <= IsDate, Value)
Hi.
Maybe the good way all calculation do in script like below:
Data_01:
LOAD * Inline [
DATE, VALUE
01.01.2015, 1
02.01.2015, 10
03.01.2015, 55
04.01.2015, 33
05.01.2015, 5
06.01.2015, 99
07.01.2015, 100
08.01.2015, 110
09.01.2015, 200
10.01.2015, 201
11.01.2015, 50
12.01.2015, 60
13.01.2015, 70
14.01.2015, 900
15.01.2015, 1
16.01.2015, 2
17.01.2015, 888
18.01.2015, 55
19.01.2015, 301
20.01.2015, 232
21.01.2015, 304
22.01.2015, 304
23.01.2015, 305
24.01.2015, 306
25.01.2015, 307
26.01.2015, 404
27.01.2015, 504
28.01.2015, 604
29.01.2015, 304
30.01.2015, 304
31.01.2015, 304
01.02.2015, 12
02.02.2015, 2
03.02.2015, 52
04.02.2015, 3
05.02.2015, 5
06.02.2015, 9
07.02.2015, 10
08.02.2015, 11
09.02.2015, 20
10.02.2015, 12
11.02.2015, 2
12.02.2015, 6
13.02.2015, 7
14.02.2015, 92
15.02.2015, 1
16.02.2015, 2
17.02.2015, 82
18.02.2015, 5
19.02.2015, 32
20.02.2015, 22
21.02.2015, 3
22.02.2015, 3
23.02.2015, 3
24.02.2015, 30
25.02.2015, 32
26.02.2015, 4
27.02.2015, 5
28.02.2015, 65
];
STORE * from Data_01 into Data_01.qvd (qvd);
DROP Table Data_01;
Calendar:
LOAD Concat( Distinct num(DATE), ', ') as DATE_01
From Data_01.qvd (qvd);
LET v_Date = FieldValue('DATE_01', 1);
DROP Table Calendar;
FOR Each i in $(v_Date)
Data:
LOAD $(i) as DATE,
sum(VALUE)
FROM Data_01.qvd (qvd)
Where DATE <= $(i) and DATE >= $(i) - 30
Group By $(i);
NEXT i
Left Join (Data)
LOAD DATE,
VALUE
FROM Data_01.qvd (qvd);