2 Replies Latest reply: May 24, 2013 2:19 PM by robynbroo RSS

    Record Counts Question - assistance please!

      Issue:  When I create a TEXT OBJECT I do not get the same results for the number of rows on a STRAIGHT CHART.


      The straight chart report shows 2,768 records.  The calculated total count for the list report shows 2,768 correctly when I use DISTINCT 

      Formula is =Sum(DISTINCT( {$< ReqHistStatusValue={3}, TimePeriodIndex={7} >} Flag_ReqCounter

        (note - I used DISTINCT because some of the values for Flag_ReqCounter are > 1 and I only want to count the record once)


      However, when I create a TEXT OBJECT and take out DISTINCT I am getting a total record count of 3,198

      Formula without DISTINCT is =Sum(( {$< ReqHistStatusValue={3}, TimePeriodIndex={7} >} Flag_ReqCounter

      How do I create formula for TEXT OBJECT to get the correct count of 2,768 where I am only counting Flag_ReqCounter only once per record?

      Do I need to use Aggregate function?


      Please see attached document with the example.

        • Re: Record Counts Question - assistance please!
          Gysbert Wassenaar

          Table boxes and straight tables and pivot tables don't show duplicate records as you noticed. Generally you can get the row count of a chart with count(aggr(1, Dim1, Dim2,... DimN)). In case one of your dimensions has null values you can try something like count({<Dim1={'*'}>} aggr(1, Dim1, Dim2,... DimN)). If you use set modifiers in your chart expressions then add those to the set modifier in the count expression. count({$< ReqHistStatusValue={3}, TimePeriodIndex={7} >} aggr(1, Dim1, Dim2,... DimN))


          edit: sum(aggr(1, Dim1...etc) works just as well as count. See for yourself if you can spot a difference in performance.

            • Re: Record Counts Question - assistance please!

              It works when I update formula to

              =count (aggr (sum ( {$<ReqHistStatusValue={3}, TimePeriodIndex={7} >} Flag_ReqCounter), AutoReg)


              However, I recall from training that counts slow down the server and it is not recommended to use them.

              In this case though I think this is the only way?  Is that true?  Is there another way in this instance by using SUM instead?