4 Replies Latest reply: Oct 22, 2012 4:29 AM by Thomas Folgerø RSS

    Chart count expression

      Hi,

       

      I have the following data:

       

      CustomerID
      ContractID
      StartDate
      EndDate
      1613502.05.2010 00:00:00NULL
      1765412.12.2000 00:00:0001.01.2012 00:00:00
      1831731.01.2007 00:00:00NULL
      1989010.06.2002 00:00:00NULL

       

      I've set up a gauge chart with the following expression: Count ({1} DISTINCT CustomerID)

       

      The chart displays the total count of CustomerID regardless of selection, but i need to filter some more.

       

      A CustomerID can have multiple ContractIDs and also I need to verify that the EndDate is after todays date or null.

       

      I'm having a hard time figuring out the correct syntax to use in the Edit Expression window. A little help would be nice

        • Re: Chart count expression
          Johannes Sunden

          Hi,

          Syntax should be something along the lines of:

          =count({1<EndDate={'>$(=Now(0))'}>} Distinct CustomerID)

           

          However, it would be nicer to strip the time part of the dates if you are only concerned with the day and use the Today() function to match your EndDates to.

          You might also want to create a CustomerFlag or something if you have a separate table with your customers with the value 1 for each unique customer. This way you can use Sum(CustomerFlag) which is a lot more responsive than using Count(Distinct CustomerID)

            • Re: Chart count expression

              Thanks for replying!

               

              Just to make sure we're on the same page: CustomerID appears several times as some customers have several contracts. Different customers in some case have the same ContractID as their one contract or as one of their many contracts.

               

              I might be missing the point, but I don't see how a CustomerFlag would help as I need to count every active contract. Contracts are considered active if their startdate is today or in the past and the enddate is in the future.