# Qlik Sense App Development

New Contributor II

## EXCEL SUMIFs to Set Analysis

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

5 Replies
Partner

## Re: EXCEL SUMIFs to Set Analysis

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:
A,date#(B,'DD-MM-YYYY') as #Date,num(date#(B,'DD-MM-YYYY')) as #Num_Date,C,D,E,F,G,H,I;
[
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:

Highlighted
Valued Contributor

## Re: EXCEL SUMIFs to Set Analysis

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.

Partner

## Re: EXCEL SUMIFs to Set Analysis

Hi Michel,

I'm unable to open other people QVWs.

Thanks
Valued Contributor

## Re: EXCEL SUMIFs to Set Analysis

Michiel

Data:
AutoNumber(B) as dateid,
Date(Date#(B, 'DD-MM-YYYY')) as Bdate,
WildMatch(A, '*'&G&'*') as matcher;
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
AutoNumber(Bdate) as Dateid
Resident Data
order by Bdate asc;

DROP Table Data;
RENAME Table Data2 to Data;

tmpasof:
NoConcatenate
Bdate
Resident Data;

Join (tmpasof)
Resident tmpasof;

[As-Of Calendar]:
NoConcatenate
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)

Valued Contributor

Larsrosenm