2 Replies Latest reply: Feb 3, 2012 6:46 AM by Matt Cayford RSS

    Inline tables and combining count and sum..

      Hello all,

      Some great karma on offer to anyone that can solve this for me...

       

      An external developer has created some code for our QV database which uses the following in the Script..

       

       

      Load * Inline
      [Select4,Equ7,Equ8
      NCO Enquiries,Count(,"IF(referredby = 101030294,referraldate))"
      ]
      

       

      and this in an Expression of a Chart...(basically where Week ClassNum = 1 , count the above enquiry criteria)

      $(=Equ7) {<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum))}>} $(=Equ8)
      


      to basically count the number of enquiries recieved in the latest week. I now have another field ENQ which needs to be simply totalled (The date function is already working)

      Something like the below

       

      Load * Inline
      [Select4,Equ7,Equ8
      NCO Enquiries,Count(,"IF(referredby = 101030294,referraldate))"(+SUM(,ENQ))
      
      

      Any ideas please?

       

      Thanks,

      Matt

        • Inline tables and combining count and sum..
          Stephen Redmond

          Hi Matt,

           

          Probably just need to extend the model a bit:

           

          Load * Inline

          [Select4,Equ7,Equ8,Equ7B,Equ8B

          NCO Enquiries,Count(,"IF(referredby = 101030294,referraldate))",SUM(,ENQ)

          ];

           

          Then you can include in the expression:

           

          $(=Equ7) {<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum))}>} $(=Equ8) + $(=Equ7B) {<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum))}>} $(=Equ8B)

           

           

           

          Regards,

           

           

          Stephen

            • Re: Inline tables and combining count and sum..

              Thanks Stephen,

              I believe I have already managed to get around it by adapting existing code in the script:

               

              Load * Inline
              [Select4,Equ7
              NCO Enquiries,"(Count({}IF(referredby = '101030294',(referraldate)))+Sum({}ENQ))"
              ];
              


              and in the expression:

               

              =$(=replace(Equ7,'{}','{<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum))}>}'))
              


              But I will save your solution as well for future development as I can see that will be very useful for other areas.

              Thanks again.