1 Reply Latest reply on Aug 2, 2018 3:11 AM by Tim Dines

    Filtering on a subset of data

    Wingyan Yip

      Version: Tableau 2018.1

       

      Hi! Am new to Tableau and hope this is not too straightforward a question!

      I am trying to work on a dataset of agent performance. Each agent participated in one or more campaigns each month/ year, each campaign can last up to 4 months.

      We want to calculate their score (based on their activities such as meeting up with clients) on a rolling basis, in the middle of M4.
      The algorithm:
      Agents' performance in campaigns that started in M1 and M2 which are done before 15th of M4 will be taken into calculation.

       

      (Campaigns that start in M3 are not taken into account)

       

      I could create a parameter (evaluation date: 15th day of all months) and a calculated field to capture all performance for campaigns in M1 and M2 (Query A):

      datediff("month",datetrunc("month",[startdate]),DATETRUNC("month",[EvaluationDate]))= 3

      or datediff("month",datetrunc("month",[startdate]),DATETRUNC("month",[EvaluationDate]))= 2

       

      then I got 300+ agents who participated in M1 and M2, M1 only and M2 only

       

      But the problem is I only want these two kinds of agents:1. Participated in M1

      2. Participated in both M1 and M2.

       

      I don't want agents who only participated in M2.

       

      I tried adding an agentcode filter and set the following condition (Query B)

      ((max(datediff("month",datetrunc("month",[startdate]),DATETRUNC("month",[EvaluationDate])))=3 and

      min(datediff("month",datetrunc("month",[startdate]),DATETRUNC("month",[EvaluationDate])))=2) and

      countd(month(startdate))=2) or

      (min(datediff("month",datetrunc("month",[startdate]),DATETRUNC("month",[EvaluationDate])))=3 and

      countd(month(startdate))=1)

       

      but i got instead of 300+ entries in Query A I got only 2 rows.
      I think what tableau did is filtering on the entire dataset the agents who only participated in 2 months/ 1 month of campaigns, and then on that dataset sieving out agents whose fulfills the date condition

       

      So for example (taking the first clause of the code in query B)

      if i want dec and jan (evaluated in mar), I apply the filter and get only agents who participated in two campaigns

      Agent A: dec and apr,

      Agent B feb and mar

      Agent C apr and june

       

       

      and my output gives me agent A's results in dec.

       

      How can I filter out the agents who participated in dec and jan first, and then filter those who have participated both dec and jan and dec only?