3 Replies Latest reply: Oct 25, 2012 10:03 AM by Chris Hopkins RSS

    Expression help

    Chris Hopkins

      Hi,

       

      I am trying to create a pivot table showing job details. I don't want to show any jobs with a JobFinishDate of xx/xx/xxxx 23:59:59 (X being the date)

       

      here is an example..

       

      Capture.JPG

       

      Does anyone know how i can hide this value please?

      I was thinking of making the JobFinishDate an expression and doing an IF statement but i want it to ignore the date part of the timestamp and only exclude values matching 23:59:59 on the time part.

       

      Thanks

        • Re: Expression help
          Mayil Vahanan Ramasamy

          HI

           

          Try like this

           

          load *,if(text(time(JobFinishDate))='23:59:59',1,0) as RegionFlag Inline

          [

          CompletedRegion,RegionSales,JobFinishDate

          Central,1000,'23/12/2011 12:11:45'

          Central,1000,'23/12/2011 23:59:59'

          ];

           

          Then in expression, use like this

          =Sum({<RegionFlag = {0}>}RegionSales)

           

          From that, we can exclude the  xx/xx/xxxx 23:59:59 in table.

           

          Hope it helps

           

          Regards,

          R.MayilVahanan