Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
MVP
MVP

Sum, count and aggr funtion

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

Community Browser