Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Got it!
=SUM(aggr(count({$<CON1= {'1'}, CON2 = {'2'}, CON3 = {'3'}>} DISTINCT DOC_NO), DATE))/COUNT(DISTINCT DATE)
tahnks for your time
See if this helps:
=RangeAvg(Aggr(Count({$<CON1= {'1'}, CON2 = {'2'}, CON3 = {'3'}>} DISTINCT DOC_NO), DATE))
Use
ALT(YourExpression,0)
Manish do you think this will work?
=Avg(Alt(Aggr(Count({$<CON1= {'1'}, CON2 = {'2'}, CON3 = {'3'}>} DISTINCT DOC_NO), DATE), 0))
This will give 0 if expression result is null...
Bcoz Avg(0) = 0
What about the good old
count({...} distinct DOC_NO) / count (distinct DATE)
I'm sending my data model, and problem
I have 3 tables:
1. LINES - with DOC deatails, the most important are:
2. REF DATE - indirect table that contain:
3. The third table is just dictionaty for REF_DATE_MOD
Got it!
=SUM(aggr(count({$<CON1= {'1'}, CON2 = {'2'}, CON3 = {'3'}>} DISTINCT DOC_NO), DATE))/COUNT(DISTINCT DATE)
tahnks for your time
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