10 Replies Latest reply on Apr 28, 2017 5:56 AM by Hajar kaya

    How to count number of records based on condition at a calculated value? (Tableau 10.2)

    Hajar kaya

      Hello everyone,

      I am Tableau's newbie  and I am facing a problem of counting number of records based on condition in a calculated value:
      My Data looks like this: ( sorry for not being able to upload the workbook)

      Country ||   store   ||   % of scapped parts
      ---------------------------------------------
      * China   ||  S1        ||         0.45
      *  China   ||     S2       ||         0.28
      *   China    ||   S3        ||         0.6
      * France  || F1          ||   0.7
      * France      ||    F2    ||         0.23
      * France      ||  F3      ||       0.88
      * France   ||   F4     ||        0.13

      I want to get the number of records of each region based on condition about the % of scrapped parts.

      I created a calculated field in wich I put: IF % scrapped parts>0.5 THEN "% scrapped parts>0.5" ELSEIF % scrapped parts>0.3 THEN "scrapped parts BETWEEN" ELSE "% scrapped parts<0.3" END

      That gaves me the 3 columns corresponding to conditions I want BUT when I drag COUNTD(store) to the table to calculate number of stores in each category for each region, it does not work correctly.
      I could just have (for china for example):
      Country||store||% scrapped parts<0.3||% scrapped parts>0.5||% scrapped BETWEEN
      ------------------------------------------------------------------------------
      *China    || S1     ||      0     ||              0         ||            1

      *China    ||      S2   ||        1   ||                0     ||            0

      *China      ||  S3      ||     0      ||             1      ||             0

      bUT i WANT to have total per country per category ( no need to show details per store):(when I remove store from rows , the results shown are wrong)
      , what I want is to get results wich look like:

      Country||% scrapped parts<0.3||% scrapped parts>0.5||% scrapped BETWEEN ||
      -------------------------------------------------------------------------
      *China        ||  1              ||        1        ||              1

      *France        || 2                ||      2         ||             0

      Please help, I could not move forward as I am blocked in this stage.
      Thank you so much!
      Regards