Get Total Days by Day of Week

[BITSMH]

Hello! I have tried to find other posts similar to my issue, but so far I haven't seen any. 

I am trying to get the total days by day of week - for example, for April 2022 Sun. = 4, Mon. = 4, Tue. = 4, Wed. = 4, Thu. = 4, Fri. =5, Sat. = 5. I have gotten close by just pulling the distinct count of the day column, but the way I have the stacked column chart set up it's not quite right. 

This is what I have: 

Ignore the title of the chart, I'm trying to just get the total days right now so I can use those to get the averages visits by day of week. This chart has the day of week on the x-axis, the distinct count of days from my orders table as the y-axis, and the legend is category. On Friday, it shows 5 for all categories except Mammography which is 4 - I'm sure this is because I'm counting the dates and there probably was one Friday in April that there were no Mammo orders. 

How can I get every chunk to be the correct number of days? Or am I way overthinking this and there is an easier way to get the average by day of week? 

Thank you!

[amitchandak · Accepted solution]

@BITSMH , if the category is coming from a separate table I think join is missing.

Also In you date table have column like

weekday = format([Date], "dddd")

and use that on axis