1

Count of records +/- x% of the average

I need to show the min, average, max rate with a count of records that fall above or below the average rate. I have tried using the following formula but it doesn't work:

count(
    if(
        staffing.Person_Job_Current.HourlyPayRate < average(
            staffing.Person_Job_Current.HourlyPayRate,
            groupBy(
                staffing.Person_Job_Current.JobCode
            )
        ),
        1,
        0
    )
)

Any guidance would be helpful.

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Marc Paige this is not supported.

    You cannot have aggregated function inside another aggregated function.

    In this case you cannot have average inside count.

    Like 1
      • Marc Paige
      • GC Services
      • Marc_Paige
      • 2 wk ago
      • 1
      • Reported - view

      Anurag Malik Okay. The next thing I tried was an MV for the average per jobcode. I joined to the person table via jobcode. The new formula looks like this:

      count(
          if(
              staffing.Person_Job_Current.HourlyPayRate < staffing.AveragePayRate.HourlyPayRate,
              1,
              0
          )
      )

      Still does not count correctly.

      Like 1
  • Marc Paige replace count with sum.  

    count is also counting 0.

    Like 1
      • Marc Paige
      • GC Services
      • Marc_Paige
      • 2 wk ago
      • 2
      • Reported - view

      Anurag Malik I had to change to an MV with Spark SQL from an Incorta Analyzer base for the MV and switch the join direction, but it now works as expected. Thanks for your help!

      Like 2
Like1 Follow
  • Status Answered
  • 1 Likes
  • 2 wk agoLast active
  • 4Replies
  • 15Views
  • 2 Following

Product Announcement

Incorta 5 is now Generally Available