Average

Hiraayub
Hiraayub Fort Myers, FL ⚪️

I have created a Card for volume by hour.

Now the department wants a card Average volume by hour.

COUNT(`Case Number`) OVER (PARTITION BY HOUR(`Created Date`))


I'm not sure if I'm doing it right as it's creating duplicate Hours and Volume.

I have done it in ETL and getting the result but then I cant do filters as It's a group by function.

I want to do it in the beast mode so I can apply the filters for the drill own.

Anyone has any idea?

Best Answer

  • GrantSmith
    GrantSmith Indiana 🥷
    Answer ✓

    @Hiraayub

    Sadly this isn't possible as you're wanting to aggregate (average) an aggregate (count) which isn't possible. The only way to get close would be to pre-aggreagate your data in an ETL and then take the average in your card but that's not what you're wanting since you want filters to be applied.



    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    Is there anything in your sorting properties? That is often a common cause as to why it is not grouping as expected.




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Hiraayub
    Hiraayub Fort Myers, FL ⚪️

    I was sorting it by hour and if you don't then that's how it's appear.


  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    @GrantSmith is the king of window functions. He will be able to tell you what to do.




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Hiraayub
    Hiraayub Fort Myers, FL ⚪️

    Thanks @MarkSnodgrass , Looking forward to meet you all in March. Hope you are coming :)

  • Hi @Hiraayub when you say you want a the average volume by hour, do you mean the average cases within, say hour 1 and hour 2, across multiple days? If so, try changing your beast mode to partition by day instead of by hour, since your data is already separated by hour in the x-axis.

     COUNT(`Case Number`) OVER (PARTITION BY DATE(`Created Date`))
    
  • Hiraayub
    Hiraayub Fort Myers, FL ⚪️

    Thanks @MichelleH, I did try that before but the result is the same.


  • Hiraayub
    Hiraayub Fort Myers, FL ⚪️

    @GrantSmith I knew that but I was hoping there was another way to work around it.

    Thank you.