2 Replies Latest reply on Apr 23, 2018 2:38 PM by Jeffrey Kritzman

    Table/LOD Calc to COUNTD values in a month, that is impacted by filters?

    Adrian LePique

      Hi Tableau Community!

       

      I've got a data set that has support-related details like:

       

      Ticket #Agent IDTeamCustomer IDStart DateEnd DateRegion
      TK123Agent 1Team 1Customer AJanuary 1, 2018January 31, 2018USA
      TK234Agent 1Team 1Customer BJanuary 15, 2018January 31, 2018CAN
      TK345Agent 2Team 1Customer CJanuary 1, 2018February 15, 2018EUR
      TK456Agent 3Team 2Customer AJanuary 2, 2018February 2, 2018EUR
      TK567Agent 3Team 2Customer CJanuary 9, 2018February 20, 2018AP

       

      I need a calculation that will distinct count the # of agents in a month.....even if I aggregate views at a lower level (day).  BUT I also need to have this calculation impacted by filters.  So in the case of the above, the count in the views should show:

      • 2 completed tickets in January for Team 1
      • 1 completed ticket in February for Team 1
      • 2 completed tickets in February for Team 2

       

      However, if I filtered on EUR region, I'd expect to only see:

      • 1 completed ticket in February for Team 1
      • 1 completed ticket in February for Team 2

       

      I tried using a FIXED calc on DATETRUNC("month",[End Date]), and the issue is it works whenever the view is unfiltered, but includes filtered values since it's fully FIXED. 

       

      Any ideas on how I could do this?  It seems like a simple ask (always having a monthly count in the view no matter what's being aggregated), but I can't seem to get this to work.  Let me know if you all have any ideas, thanks!