Aggr count null instead 0

[arsenal1983]

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]

See if this helps:

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

[MK_QSL]

Use

ALT(YourExpression,0)

[sunny_talwar]

Manish do you think this will work?

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

[MK_QSL]

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

Bcoz Avg(0) = 0

[Anonymous]

What about the good old

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

[arsenal1983]

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:

• REF_DATE_KEY - from LINES table
• REF_DATE_MOD - the dates when the doc was open
• DIFF between CPUDT and REF_DATE_MOD

3. The third table is just dictionaty for REF_DATE_MOD

[arsenal1983 · Accepted solution]

Got it!

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

tahnks for your time

[Anonymous]

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