Average

Hiraayub
Hiraayub Member
in Charting

I have created a Card for volume by hour.

Domo_1.PNG

Now the department wants a card Average volume by hour.

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


Domo_2.PNG

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?

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    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 Coach

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Hiraayub
    Hiraayub Member

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

    Domo_3.PNG


  • MarkSnodgrass
    MarkSnodgrass Coach

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Hiraayub
    Hiraayub Member

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

  • MichelleH
    MichelleH Coach

    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`))
  • GrantSmith
    GrantSmith Coach
    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!**
  • Hiraayub
    Hiraayub Member

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

    Domo_4.PNG


  • Hiraayub
    Hiraayub Member

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

    Thank you.

Categories