topic Re: Record Counts Question - assistance please! in QlikView

Record Counts Question - assistance please!

Fri, 24 May 2013 17:28:16 GMT

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 — Fri, 24 May 2013 17:57:37 GMT

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!

Fri, 24 May 2013 18:19:25 GMT

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?