topic Re: EXCEL SUMIFs to Set Analysis in App Development

EXCEL SUMIFs to Set Analysis

larsrosenm — Wed, 27 Feb 2019 14:53:12 GMT

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

Re: EXCEL SUMIFs to Set Analysis

felipedl — Wed, 27 Feb 2019 19:42:18 GMT

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:

Re: EXCEL SUMIFs to Set Analysis

Michiel_QV_Fan — Fri, 01 Mar 2019 11:27:30 GMT

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.

Re: EXCEL SUMIFs to Set Analysis

felipedl — Fri, 01 Mar 2019 14:54:31 GMT

Hi Michel,

Can you share your code please?
I'm unable to open other people QVWs.

Thanks

Re: EXCEL SUMIFs to Set Analysis

Michiel_QV_Fan — Mon, 04 Mar 2019 06:28:33 GMT

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)

Re: EXCEL SUMIFs to Set Analysis

Michiel_QV_Fan — Fri, 22 Mar 2019 15:04:52 GMT

Larsrosenm

did you find your answer in our answers?