Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

larsrosenm
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.

 

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

 

5 Replies
Partner
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:
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

michielvandegoo
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.

2019-03-01 12_25_34-Window.jpg

Partner
Partner

Re: EXCEL SUMIFs to Set Analysis

Hi Michel,

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

Thanks
Highlighted
michielvandegoo
Valued Contributor

Re: EXCEL SUMIFs to Set Analysis

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)

michielvandegoo
Valued Contributor

Re: EXCEL SUMIFs to Set Analysis

Larsrosenm

did you find your answer in our answers?