Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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