Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
larsrosenm
Contributor II
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.

 

ABCDEFGHI
28012019|10041|27531228-01-2019100412753126122Not Started10041|27531211
29012019|10041|27531229-01-2019100412753126122Not Started10041|27531212
30012019|10041|27531230-01-2019100412753126122Not Started10041|27531213
31012019|10041|27531231-01-2019100412753126122Not Started10041|27531214
01022019|10041|27531201-02-2019100412753126122Un assigned10041|27531215
04022019|10041|27531204-02-2019100412753126122Not Started10041|27531216
05022019|10041|27531205-02-2019100412753126122Not Started10041|27531216
06022019|10041|27531206-02-2019100412753126122Not Started10041|27531216
07022019|10041|27531207-02-2019100412753126122Not Started10041|27531216

 

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

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:

sample.png

View solution in original post

5 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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:

sample.png

Michiel_QV_Fan
Specialist
Specialist

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.

2019-03-01 12_25_34-Window.jpg

felipedl
Partner - Specialist III
Partner - Specialist III

Hi Michel,

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

Thanks
Michiel_QV_Fan
Specialist
Specialist

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)

Michiel_QV_Fan
Specialist
Specialist

Larsrosenm

did you find your answer in our answers?