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

# 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!

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?