1 Reply Latest reply on Jun 19, 2017 6:22 AM by Zhouyi Zhang

    Exclude certain values from a SUM() and/or AVG() function, etc.

    Shaun Swanepoel

      Hi there Tableau Community

       

      Trying to create a calculated field that will return the SUM(Rating) when the following is met:

       

      IF::

       

      Is only a view on the account, by agent and date, then return the Rating.

      Is only an action on the account, by agent and date, then return the Rating.

      Is an action AND a view on the account, by agent and date, then return only the Rating of Actions and not views eg:

       

      Date     Agent     Account     Type     Rating     <*note*>

      2017-05-01     John     Account10101     Action     10 <*sum  this*>

      2017-05-01     John     Account10101     View    10 <*don't sum this*>

       

      This should ONLY sum the Action, and not the View, by a fixed level on date,account,agent.

       

      Basically, if there is both a view and an action in a workflow, then, only the Rating of the action applies and the rating of a view does not apply at all.

       

      I need help to aggregate this into a SUM so I am able to work out an Average Rating based on 100-avg rating to give a final Score.

       

      Any help on how to exclude the specfic rows from the SUM would be appreciated greatly.

       

      I would like to have a SUM on a date,account,agent level for [if only action then sum],[if only view, then sum],[if view and action, only sum action not view].

       

      Any assistance will be greatly valued. I have attached the sample data and a .twbx and everything needed.

       

      Cheers,

       

      Shaun