5 Replies Latest reply on Feb 15, 2017 4:23 PM by eveline noyb

    Counting hierarchy - making sure unique ID's are only counted in one category

    eveline noyb

      Hi there,

       

      I've been messing around for two days now and can't seem to find the solution to my problem. The solutions found on the forum are all slightly different to my case.

       

      Unfortunately I can't share workbooks as I work with tableau only on a different computer.

       

      My data is set up as follows:

       

      Unique ID     Category      Financial year

      A1                    AF               2012

      A2                    AF               2012

      A2                    P                 2012

      A2                    AF               2013

      A3                    PO              2012  

       

      I want to count Unique ID's per category and by Financial year. When Unique ID's have two or more categories listed, I would like to give preference/priority to the AF Category and have them only counted for that category.

       

      In tableau Financial year is in the columns, Category on the rows and COUNTD(Unique ID) is on the Marks shelve as text.

       

      My Grand total shows the correct values, namely a distinct count of Unique ID's. However, Unique ID's that fall in more than one category are simply counted in both categories, for instance AF and P.

       

      I have tried several LOD calculations and subsetting data, but it hasn't helped me so far. I have also tried to create a flag that identifies Unique ID's that fall into two categories (for instance AF and P), but because the Unique ID is simply duplicated this does not work. the LOD below also does not achieve the result because of the duplicate rows:

       

      {FIXED [UNIQUE ID] : MAX( IF ([CATEGORY]='AF' AND [CATEGORY]='P') THEN [UNIQUE ID] END ) }

       

       

      I would really appreciate any help on this matter!