2 Replies Latest reply on Apr 8, 2017 10:49 AM by Glenn Kuly

# Calculatings Number of Weekdays In A Month Using a Parameter

I am trying to calculate total monthly sales per weekday (and hour), using a parameter to select a chosen month.

Thanks to Tom W  for demonstrating how to achieve this by using a filter to select the month, as shown below. But I'm stuck on how I can use a parameter rather than a filter. Using a CASE statement or IF [Date (Month/Year)] = [Date (Months)] THEN COUNTD([Date1]) END isn't possible (i.e. mixing aggregate and non-aggregate elements).

Can someone suggest an approach that will work? I've attached a sample workbook (Tableau Desktop 10.1).

-- Glenn

calculate the number of each weekday in a chosen month

• ###### 1. Re: Calculatings Number of Weekdays In A Month Using a Parameter

hi Glenn,

So in order to do this we need to apply the "in formula condition" at a row level (thus getting round the Agg/Non-Agg problem) and then aggregating the result using COUNTD...

So the formula would be

COUNTD(IF [Date (Month/Year)] = [Date (Months)] THEN [Date1] END)

The reason this works is that at a row level, we are populating each row, with either the date (Date1), if it is in the parameter-selected month, or NULL if it isn't (as there is no ELSE clause anything not meeting that criteria gets a NULL). When we then do a COUNTD on this, NULLs aren't counted.

Hope that solves your problem, and makes sense, but let me know if not

1 of 1 people found this helpful
• ###### 2. Re: Calculatings Number of Weekdays In A Month Using a Parameter

Works perfectly, thank you!

-- Glenn

1 of 1 people found this helpful