Average by weekday in line chart

[ioannagr]

Hi all! 

So i'm faced with a new problem today...

Look, I need to calculate the average of  ids by weekday. 

dimension is weekday in this line chart.

and for measures i tried

avg(aggr(count({<id={"=len(id)=12"}>} id),Weekday) but doesn't give correct results.

sum(aggr(count({<id={"=len(id)=12"}>} id),Weekday)) / count (distinct DayOftheWeek) and doesn't work.

I'm out of ideas... it just calculates the raw count by weekday.

Help?

[GaryGiles]

You want to use the Total modifier to include all values regardless of dimesion row.  Try this:

avg(Total aggr(count({<id={"=len(id)=12"}>} id),Weekday)

You may want to add distinct if you want distinct ids.

avg(Total aggr(count({<id={"=len(id)=12"}>} distinct id),Weekday)

[ioannagr]

@GaryGiles hello i appreciate your help, but this suggestion gives me on every weekday the same number 😕

[GaryGiles · Accepted solution]

Sorry, I misunderstood what you were asking.  If your chart dimension is WeekDay (Sun, Mon, Tue, Wed, Thu, Fri, Sat), I think you need to aggregate based on the actual date.

avg(aggr(count({<id={"=len(id)=12"}>} id), DateField) )

where DateField is the date of the transactions.  That should give you the average count for Dates that are a Sunday and so on.

[ioannagr]

Ok @GaryGiles , would the same expression be when i want to see that line chart by month too or weekyear?

[GaryGiles]

Yes, if you change the chart dimension to month or weekyear, the expression should give you the average count of IDs per date in that time period.

[ioannagr]

For month , i did it and it gives the average per day in this month. Maybe there is another way?

@GaryGiles 

I kept the measure as you proposed, i changed the dimension to month , i picked 2020 and 2021 and Jan in the filters for validation and i expected 40k, while this gave me 40k/62 (the distinct days that is) so around 1k.

[GaryGiles]

"i did it and it gives the average per day in this month".   Describe what you were looking for it to give you.

[ioannagr]

okay my bad, i want to get the avg for say, Januarys.

Like if i pick  in filters Jan 2021 and Jan 2020 and

Jan 2021: count({<id={"=len(id)=12"}>} id)= 5000

Jan 2020: count({<id={"=len(id)=12"}>} id)=3000

i want the avg to be (5000+3000)/2=4000.

[GaryGiles]

Okay, in that case, you would want:

avg(aggr(count({<id={"=len(id)=12"}>} id), MonthField) )

Keep in mind that the field that you specify in the aggr() will work in conjunction with any Dimension field you have setup in your chart object.