Skip to main content
Answer

Report Designer Aggregates (Average with Filters)

  • October 28, 2024
  • 1 reply
  • 72 views
Michaelh
Pro I
Forum|alt.badge.img+2

I have formula that can grab all the differences in expected vs real dates, but how do I tell my AVG() aggregate to ignore any zero values in the formula (i.e. the false branch of the statement below)?

 

=AVG(
  iif(

DateDiff('d',[POOrder.ExpectedDate],[POOrder.UsrOrigPromDate])>6 
OR 
DateDiff('d',[POOrder.ExpectedDate],[POOrder.UsrOrigPromDate])<-6,

DateDiff('d',[POOrder.ExpectedDate],[POOrder.UsrOrigPromDate]),

0))

I tried using the empty set instead of zero, but it gets angry about it not being a number. There has to be a way to just say “Skip this row” based on a condition. Can someone save me?

Best answer by Michaelh

I was missing the obvious answer: NULL.

 

Make your false branch return NULL. Just be ready to catch the error if everything is NULL. As long as you have SOME values it’ll work without issue.

Curious how to catch the error? Use your iif conditional as a SUM and set that as a visibility flag.

 

Example using my code:

Visibilty Expression is set to:
=SUM(iif(
DateDiff('d',[POOrder.ExpectedDate],isnull([POOrder.UsrOrigPromDate],[POOrder.ExpectedDate]))>6 
OR 
DateDiff('d',[POOrder.ExpectedDate],isnull([POOrder.UsrOrigPromDate],[POOrder.ExpectedDate]))<-6,
1,0))>0

Then I made another field right on top of it, with a static value of 0 (meaning no misses for that vendor) and gave it the same Visibility expression EXCEPT that it now equals 0.

=SUM(iif(
DateDiff('d',[POOrder.ExpectedDate],isnull([POOrder.UsrOrigPromDate],[POOrder.ExpectedDate]))>6 
OR 
DateDiff('d',[POOrder.ExpectedDate],isnull([POOrder.UsrOrigPromDate],[POOrder.ExpectedDate]))<-6,
1,0))=0

Now my report shows that they either missed a shipment and I get that average number of days OR they missed nothing and therefore the answer is 0.

Permanent Newb, don't let the posts fool you.

1 reply

Michaelh
Pro I
Forum|alt.badge.img+2
  • Michaelh
  • Author
  • Pro I
  • Answer
  • October 28, 2024

I was missing the obvious answer: NULL.

 

Make your false branch return NULL. Just be ready to catch the error if everything is NULL. As long as you have SOME values it’ll work without issue.

Curious how to catch the error? Use your iif conditional as a SUM and set that as a visibility flag.

 

Example using my code:

Visibilty Expression is set to:
=SUM(iif(
DateDiff('d',[POOrder.ExpectedDate],isnull([POOrder.UsrOrigPromDate],[POOrder.ExpectedDate]))>6 
OR 
DateDiff('d',[POOrder.ExpectedDate],isnull([POOrder.UsrOrigPromDate],[POOrder.ExpectedDate]))<-6,
1,0))>0

Then I made another field right on top of it, with a static value of 0 (meaning no misses for that vendor) and gave it the same Visibility expression EXCEPT that it now equals 0.

=SUM(iif(
DateDiff('d',[POOrder.ExpectedDate],isnull([POOrder.UsrOrigPromDate],[POOrder.ExpectedDate]))>6 
OR 
DateDiff('d',[POOrder.ExpectedDate],isnull([POOrder.UsrOrigPromDate],[POOrder.ExpectedDate]))<-6,
1,0))=0

Now my report shows that they either missed a shipment and I get that average number of days OR they missed nothing and therefore the answer is 0.

Permanent Newb, don't let the posts fool you.
Chris Hackett
Terms & ConditionsCookie settingsAccessibility statement
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved