1 Reply Latest reply on May 7, 2013 1:18 PM by Mark Holtz

    how to combine dimension and measure for logical formula

    Ambi Nair

      Hi ,

       

      I have a situation where I need to filter at different levels for different regions.

       

      Data

       

      RegionCustomerPrice
      ANZC150

      C260

      C323

      C440

      C150

      C260
      KoreaC170

      C280

      C360

      C451

      C140

      C233

       

      i want to write a case formula which says,

       

      If Region='ANZ and Price > 100 then show me only those customer whose total price is more than 100 which is in this C1 and C2 should be displayed.

      If Region ='korea' and price <61 then show me only those customer whose total price is less than 61 which in this case should display only c3 and c4.

       

      Please help.

       

      Regards

      amby

        • 1. Re: how to combine dimension and measure for logical formula
          Mark Holtz

          Hello Amby,

           

          I think I understand what you want, but I don't think what you said matches your data. You said that the ANZ & >100 results should give C1 and C2, but C2 in ANZ shows just 60.

           

          You are asking to filter based on a dimension created from aggregated data, which I do not know how to do easily.

           

          But, you could manipulate your data before bringing it into Tableau. Currently, there is no distinction between the first row of ANZ+C1+50 and the second. Are these separate transactions? Do they represent a different period?

          If so, you should add a field to your data to designate that. If not, then there is no reason not to roll up your data before bringing it into Tableau.

           

          If you had a single record per Region+Customer, you could accomplish this easliy but creating a calculated field as:

          IF [Region] = 'ANZ' AND [Price] >100 THEN 'Show'

          ELSEIF  [Region] = 'KOREA' AND [Price] < 61 THEN 'Show'

          ELSE 'Hide'

          END

           

          Then you'd just need to filter on that new field.