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

    Sum, count and aggr funtion

    JUAN GONCALVES

      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

        • Sum, count and aggr funtion
          Stefan Wühl

          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