topic Re: Aggr count null instead 0 in QlikView

Aggr count null instead 0

arsenal1983 — Mon, 17 Aug 2015 10:29:12 GMT

QV Masters,

I have a problem with aggr function.

I need to count the average daily number of invoices that meets several conditions. I have used:

=avg(aggr(count({$<CON1= {'1'}, CON2 = {'2'}, CON3 = {'3'}>} DISTINCT DOC_NO), DATE))

the problem is that when count({$<CON1= {'1'}, CON2 = {'2'}, CON3 = {'3'}>} DISTINCT DOC_NO) = 0 then

aggr(count({$<CON1= {'1'}, CON2 = {'2'}, CON3 = {'3'}>} DISTINCT DOC_NO), DATE) is NULL and such days are not taken to the average.

Any suggestions?

sunny_talwar — Mon, 17 Aug 2015 10:46:06 GMT

See if this helps:

=RangeAvg(Aggr(Count({$<CON1= {'1'}, CON2 = {'2'}, CON3 = {'3'}>} DISTINCT DOC_NO), DATE))

MK_QSL — Mon, 17 Aug 2015 10:53:35 GMT

Use

ALT(YourExpression,0)

sunny_talwar — Mon, 17 Aug 2015 10:56:39 GMT

Manish do you think this will work?

=Avg(Alt(Aggr(Count({$<CON1= {'1'}, CON2 = {'2'}, CON3 = {'3'}>} DISTINCT DOC_NO), DATE), 0))

MK_QSL — Mon, 17 Aug 2015 10:58:41 GMT

This will give 0 if expression result is null...

Bcoz Avg(0) = 0

Anonymous — Mon, 17 Aug 2015 11:00:24 GMT

What about the good old

count({...} distinct DOC_NO) / count (distinct DATE)

arsenal1983 — Mon, 17 Aug 2015 12:32:55 GMT

I'm sending my data model, and problem

I have 3 tables:

1. LINES - with DOC deatails, the most important are:

DOKUMENT_KLUCZ - which is DOC_ID
CPUDT - which is the date the DOC was created into the system
AUGDT - which is the date the DOC was processed, it can be null if the DOC is still open item
REF_DATE_KEY - which is CPUDT+"\"+AUGDT

2. REF DATE - indirect table that contain:

3. The third table is just dictionaty for REF_DATE_MOD

arsenal1983 — Mon, 17 Aug 2015 13:19:44 GMT

Got it!

=SUM(aggr(count({$<CON1= {'1'}, CON2 = {'2'}, CON3 = {'3'}>} DISTINCT DOC_NO), DATE))/COUNT(DISTINCT DATE)

tahnks for your time

Anonymous — Tue, 18 Aug 2015 10:34:06 GMT

Glad it works, but my simple suggestion above returns the same result:

=count({$<REF_DATE_DATE_NULL_FLAG = {'0'}, REF_DATE_DIFF = {">=$(v0000100093)"}>} DISTINCT REF_DATE_DATE_MOD)/COUNT(DISTINCT REF_DATE_DATE_MOD)

No sum(aggr()) needed