Average

Hiraayub

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?

GrantSmith

@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.

MarkSnodgrass

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

Hiraayub

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

MarkSnodgrass

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

Hiraayub

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

MichelleH

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

@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.

Hiraayub

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

Hiraayub

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

Thank you.