I have multiple records per day and am trying to calculate the average per day. When I try to look at the average Delv Qty it pulls the average per record, not per day. I have been trying to do this with a window_ave but cannot figure out exactly how to do it. This is likely very simple but can anyone point me in the right direction?
Thanks,
hi Logan,
So you can do this with a Table Calc, but it will mean having order level in the Viz Detail of any views you build...however this is just the kind of thing new LoD calcs were made for!!
I'm not sure at what level you want the average over, so I've just done a total daily average (in your final solution you could add more dimensions to the FIXED, such as Driver)
The LoD calc is
{FIXED[Ticket Date]: SUM([Delv Qty]) }
so this takes the SUM of Delv Qty for each day, and then puts that back against each each day at Row Level. This means that we can then further aggregate the result (in your case with an Average). If you wanted to have this aggregated figure calculated by, say Day and Driver it would be
{FIXED[Ticket Date], [Driver]: SUM([Delv Qty]) }
It's a little bit like SUMIFS in Excel, if you've used these before.
Hope this is what you were after and makes sense, but if not please post back
