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: 
Not applicable

Sum, count and aggr funtion

Hi Gurus, firstof all sorry for my English, is not my natural language,  currently I have a resume table with 2dimensions customer and week (WEEK_DT) and an expression -> sum(qty),something like this:

CUSTOMER

 

WEEK

 

20

 

21

 

22

 

23

 

24

 

25

 

26

 

27

 

28

 

29

 

30

 

A

 

 

5

 

4

 

0

 

9

 

8

 

11

 

0

 

20

 

36

 

1

 

0

 

B

 

 

10

 

22

 

33

 

2

 

95

 

20

 

14

 

0

 

13

 

8

 

0

 

C

 

 

20

 

10

 

0

 

0

 

50

 

-20

 

15

 

0

 

0

 

0

 

0

 

D

 

 

10

 

30

 

-30

 

0

 

0

 

0

 

0

 

15

 

10

 

0

 

0

 

E

 

 

0

 

0

 

0

 

0

 

8

 

0

 

0

 

0

 

0

 

0

 

5

 

F

 

 

0

 

0

 

15

 

20

 

14

 

8

 

9

 

10

 

-10

 

-10

 

20

 

G

 

 

41

 

33

 

-5

 

10

 

8

 

77

 

0

 

5

 

0

 

0

 

0

 

Here I don’thave problem with this, but when I try to do another resume table that show if  exists any transaction in the last 3 weeks ,the function no shows the correct value:

CUSTOMER

 

SERVICE  LAST 3 WEEK

 

WEEKS  WITH SERVICE

 

A

 

Y

 

2

 

B

 

Y

 

2

 

C

 

N

 

0

 

D

 

Y

 

1

 

E

 

Y

 

1

 

F

 

Y

 

3

 

G

 

N

 

0

 

These are the expressions:

SERVICE LAST 3 WEEKS:IF(COUNT(DISTINCT IF(WEEK_DT>=MAX(TOTAL WEEK_DT) -2 AND AGGR(SUM(QTY),<CUSTOMER>)<>0,IF(QTY<>0,WEEK_DT)))>0,'Y','N')

WEEKS WITH SERVICE: COUNT(DISTINCT IF(WEEK_DT>=MAX(TOTAL WEEK_DT)-2 AND AGGR(SUM(QTY),<CUSTOMER>)<>0,IF(QTY<>0,WEEK_DT)))


The AGGR functionis not working….

Anysuggestion??

Thanks inadvance

1 Reply
swuehl
MVP
MVP

Hi Juan,

<CUSTOMER> should probably be [CUSTOMER] or just CUSTOMER.

And... Do you need the last if() statement (i.e. if(QTY <> 0,WEEK_DT) ) or shouldn' just a WEEK_DT be enough?

In total something like:

=COUNT(DISTINCT IF(WEEK_DT>=MAX(TOTAL WEEK_DT)-2 AND AGGR(SUM(QTY), CUSTOMER)<>0,WEEK_DT ))

Haven't tested this though.

Regards,

Stefan