1 Reply Latest reply on Jun 7, 2012 3:18 AM by Jonathan Drummey

    Distinct filter?

      I have a de-normalized data source containing pay data - ie. id number, name, pay dates, hours.


      I am trying to get the average hours by different groupings (ie, standing) but for that average to be accurate I need each employee to only be counted once (distinct on id). In the past I've been able to put countd(id) against the attribute I'm trying to measure, but now I need to get the average out of that data that the countd would be using.


      I can't really just put the distinct on the data source as I am also filtering by pay dates.


      Are there any ideas or examples?



        • 1. Re: Distinct filter?
          Jonathan Drummey

          Hi Amanda,


          If you search the forums for "average count distinct" or "median count distinct" you can find some examples of people working with similar problems, such as this one:




          In general, to do this sort of calculation you're going to need the ID Number in the level of detail of the view in order to generate the right aggregation and average, and given that you'll probably need a table calculation as in the above example. If you're not able to work it out, I suggest posting a packaged workbook (.twbx) with some sample data and a mockup of your goal. For example, you'd said that the data source had ID Number, name, pay dates, and hours, and then you stated you want to look at the average hours by different groupings such as standing, so there are other dimensions in the data needed in the view. That would be useful to know.