Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to create a chart that displays count of recorded dates for each dimension in a bucketed week, from current date (eg. current date -previous 7days would be Week1 , previous 8 - 14 days would be Week2, etc.).. something like below;
Dimension | Week 1(0-7 days) | Week 2(8-14 days) | Total Dates |
---|---|---|---|
A | 50 | 10 | 60 |
B | 20 | 20 | |
C | 15 | 15 |
I believe Interval match would be needed with week(today()), but not sure how to put it to work..
Appreciate any help on it.
Cheers.
Hi Priyanka,
Doesnt necessarily need the intervalmatch to do it.
Attached an example with some dummy data.
Hope this is what you need.
Felipe.
Cant edit my post, so i'm pasting the code since you can be using sense rather than View.
sample:
Load * Inline
[
Id,Date,Value
A,01/07/2017,1
A,02/07/2017,2
A,03/07/2017,3
B,04/07/2017,4
B,05/07/2017,5
A,06/07/2017,6
A,07/07/2017,7
A,08/07/2017,8
A,09/07/2017,9
F,10/07/2017,10
B,11/07/2017,11
C,12/07/2017,12
D,13/07/2017,13
E,14/07/2017,13
A,15/07/2017,14
A,16/07/2017,12
C,17/07/2017,23
E,18/07/2017,2
F,19/07/2017,2
E,20/07/2017,11
B,21/07/2017,1
F,22/07/2017,1
B,23/07/2017,2
C,24/07/2017,2
D,25/07/2017,3
E,26/07/2017,54
A,27/07/2017,56
A,28/07/2017,7
C,29/07/2017,5
E,30/07/2017,4
F,31/07/2017,5
B,01/08/2017,6
C,02/08/2017,54
D,03/08/2017,4
E,04/08/2017,44
A,05/08/2017,4
A,06/08/2017,4122
C,07/08/2017,3
E,08/08/2017,1
A,09/08/2017,132
A,10/08/2017,1
A,11/08/2017,13
A,12/08/2017,123
A,13/08/2017,23
A,14/08/2017,1323
A,15/08/2017,123
];
for i = 1 to 100
x:
Load
Today()-$(i) as T,
'Week '& floor(($(i)/7+1)) as Week
AutoGenerate(1);
next i;
left join(sample)
Load
T as Date,
Week
Resident x;
drop table x;