1 Reply Latest reply: Sep 11, 2011 5:01 PM by Stefan Wühl

# 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??

• ###### 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