Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In excel I can do this formel =SUM.IF(H:H;G:G;G2;B:B;"<="&B2;B:B;">="&B2-7) in colum I for each line
If the unique value in column G, is registered between the date in column B, and the date-7 in column B, it shall sum the value in column H.
So for example the unique value in line 2, column G is registered twice between the date 29-01-2019 and (29-01-2019)-7 it then sums the value from H to 2 in column I.
How do I get this with set analysis.
A | B | C | D | E | F | G | H | I |
28012019|10041|275312 | 28-01-2019 | 10041 | 275312 | 6122 | Not Started | 10041|275312 | 1 | 1 |
29012019|10041|275312 | 29-01-2019 | 10041 | 275312 | 6122 | Not Started | 10041|275312 | 1 | 2 |
30012019|10041|275312 | 30-01-2019 | 10041 | 275312 | 6122 | Not Started | 10041|275312 | 1 | 3 |
31012019|10041|275312 | 31-01-2019 | 10041 | 275312 | 6122 | Not Started | 10041|275312 | 1 | 4 |
01022019|10041|275312 | 01-02-2019 | 10041 | 275312 | 6122 | Un assigned | 10041|275312 | 1 | 5 |
04022019|10041|275312 | 04-02-2019 | 10041 | 275312 | 6122 | Not Started | 10041|275312 | 1 | 6 |
05022019|10041|275312 | 05-02-2019 | 10041 | 275312 | 6122 | Not Started | 10041|275312 | 1 | 6 |
06022019|10041|275312 | 06-02-2019 | 10041 | 275312 | 6122 | Not Started | 10041|275312 | 1 | 6 |
07022019|10041|275312 | 07-02-2019 | 10041 | 275312 | 6122 | Not Started | 10041|275312 | 1 | 6 |
Hi,
You can do it in a way that, if the count by the G dimension is greater than 1, it sums up the field (column h) like so:
Script used:
x:
load
A,date#(B,'DD-MM-YYYY') as #Date,num(date#(B,'DD-MM-YYYY')) as #Num_Date,C,D,E,F,G,H,I;
load * Inline
[
A,B,C,D,E,F,G,H,I
28012019|10041|275312,28-01-2019,10041,275312,6122,Not Started,10041|275312,1,1
29012019|10041|275312,29-01-2019,10041,275312,6122,Not Started,10041|275312,1,2
30012019|10041|275312,30-01-2019,10041,275312,6122,Not Started,10041|275312,1,3
31012019|10041|275312,31-01-2019,10041,275312,6122,Not Started,10041|275312,1,4
01022019|10041|275312,01-02-2019,10041,275312,6122,Un assigned,10041|275312,1,5
04022019|10041|275312,04-02-2019,10041,275312,6122,Not Started,10041|275312,1,6
05022019|10041|275312,05-02-2019,10041,275312,6122,Not Started,10041|275312,1,6
06022019|10041|275312,06-02-2019,10041,275312,6122,Not Started,10041|275312,1,6
07022019|10041|275312,07-02-2019,10041,275312,6122,Not Started,10041|275312,1,6
07022019|10041|275311,07-02-2019,10041,275312,6122,Not Started,10041|275311,1,6
];
And as an expression, you have the following:
count({<#Date,#Num_Date={">=$(=max(#Num_Date)-7)<=$(=max(#Num_Date))"}>}G)
That will sum up by your key field (H column) what you need.
See the attached app and screenshot:
Hi,
You can do it in a way that, if the count by the G dimension is greater than 1, it sums up the field (column h) like so:
Script used:
x:
load
A,date#(B,'DD-MM-YYYY') as #Date,num(date#(B,'DD-MM-YYYY')) as #Num_Date,C,D,E,F,G,H,I;
load * Inline
[
A,B,C,D,E,F,G,H,I
28012019|10041|275312,28-01-2019,10041,275312,6122,Not Started,10041|275312,1,1
29012019|10041|275312,29-01-2019,10041,275312,6122,Not Started,10041|275312,1,2
30012019|10041|275312,30-01-2019,10041,275312,6122,Not Started,10041|275312,1,3
31012019|10041|275312,31-01-2019,10041,275312,6122,Not Started,10041|275312,1,4
01022019|10041|275312,01-02-2019,10041,275312,6122,Un assigned,10041|275312,1,5
04022019|10041|275312,04-02-2019,10041,275312,6122,Not Started,10041|275312,1,6
05022019|10041|275312,05-02-2019,10041,275312,6122,Not Started,10041|275312,1,6
06022019|10041|275312,06-02-2019,10041,275312,6122,Not Started,10041|275312,1,6
07022019|10041|275312,07-02-2019,10041,275312,6122,Not Started,10041|275312,1,6
07022019|10041|275311,07-02-2019,10041,275312,6122,Not Started,10041|275311,1,6
];
And as an expression, you have the following:
count({<#Date,#Num_Date={">=$(=max(#Num_Date)-7)<=$(=max(#Num_Date))"}>}G)
That will sum up by your key field (H column) what you need.
See the attached app and screenshot:
Created attached solution using an AsOf calendar and a matching. I changed value in column G on feb 5. You see the change in count there.
Michiel
Data:
LOAD *,
AutoNumber(B) as dateid,
Date(Date#(B, 'DD-MM-YYYY')) as Bdate,
WildMatch(A, '*'&G&'*') as matcher;
load * Inline [
A, B, C, D, E, F, G, H, I
28012019|10041|275312, 28-01-2019, 10041, 275312, 6122, Not Started , 10041|275312, 1, 1
29012019|10041|275312, 29-01-2019, 10041, 275312, 6122, Not Started, 10041|275312, 1, 2
30012019|10041|275312, 30-01-2019, 10041, 275312, 6122, Not Started, 10041|275312, 1, 3
31012019|10041|275312, 31-01-2019, 10041, 275312, 6122, Not Started, 10041|275312, 1, 4
01022019|10041|275312, 01-02-2019, 10041, 275312, 6122, Un assigned, 10041|275312, 1, 5
04022019|10041|275312, 04-02-2019, 10041, 275312, 6122, Not Started, 10041|275312, 1, 6
05022019|10041|275312, 05-02-2019, 10041, 275312, 6122, Not Started, 10041|275313, 1, 6
06022019|10041|275312, 06-02-2019, 10041, 275312, 6122, Not Started, 10041|275312, 1, 6
07022019|10041|275312, 07-02-2019, 10041, 275312, 6122, Not Started, 10041|275312, 1, 6
];
Data2:
NoConcatenate
LOAD *,
AutoNumber(Bdate) as Dateid
Resident Data
order by Bdate asc;
DROP Table Data;
RENAME Table Data2 to Data;
tmpasof:
NoConcatenate
LOAD Distinct
Bdate
Resident Data;
Join (tmpasof)
LOAD Bdate as AsOfBdate
Resident tmpasof;
[As-Of Calendar]:
NoConcatenate
LOAD
Bdate,
AsOfBdate,
AsOfBdate-Bdate as Difference
Resident tmpasof
Where AsOfBdate >= Bdate;
DROP Table tmpasof;
TAG Field B with $date;
And in the expression:
count({<Difference = {'<8'}, matcher = {1}>}H)
did you find your answer in our answers?