How to calculate average using SUM(DISTINCT 'field name') / COUNT(DISTINCT 'field name')

elvis0838
elvis0838
in Magic ETL

Trying to get average of the SUM(DISTINCT 'Days Excavating') / COUNT(DISTINCT 'Days Excavating').  Both field types are whole numbers.  This calculation always retuns the value for SUM(DISTINCT 'Days Excavating').  Beast Mode does not run the COUNT(DISTINCT 'Days Excavating').  I have used parenthesis around both functions and the whole calculation.  No change.  For example if the SUM(DISTINCT 'Days Excavating') = 22 and the COUNT(DISTINCT 'Days Excavating') = 11.  The average equals 2.  

 

Each function works seperately as the only function in other calculated fields.  Any thoughts how to make this work?

Best Answer

  • elvis0838
    elvis0838
    Answer ✓

    I figured a solution for this. Just like in SQL, you use the OVER clause to get other window functions to run.  So I added an OVER clause to the Count(Distinct 'field name') OVER (order by 'field name 1' asc) to get my solution.  It works as expected.

     

    Hope this helps others.

Categories