1 Reply Latest reply on Nov 6, 2017 10:43 AM by Yuriy Fal

    If Condition with Aggregate function

    Tianyue Yang

      I have a function that calculate the difference between two years for transaction count:

      [2016 Transaction Count]-[2015 Transaction Count]

       

      Now we want to expand it to an if statement, so basically something like this:

      IF Year 2015 and 2016 is selected

      Then [2016 Transaction Count]-[2015 Transaction Count]

      ELSEIF Year 2016 and 2017 selected

      Then [2017 Transaction Count]-[2016 Transaction Count]

      ELSEIF Year 2015 and 2017 selected

      Then [2017 Transaction Count]-[2015 Transaction Count]

      ELSE

      0

       

      I know this is not the best way to do the year over year calc, but I cannot get the table calculation works with a map. I thought this might be an easier way to approach it, then I cannot get the formula to work.

      The Year is a dimension and used as a filter. User can select any years they want (2015-2017).

       

      I'm still new to Tableau, any help will be appreciated.

       

      Thanks,

      Tianyue

        • 1. Re: If Condition with Aggregate function
          Yuriy Fal

          Hi Tianyue,

           

          Hope the logic behind your calculations

          could be simplified a bit in such a way:

           

          Calculate the difference in Transaction Count between

          the Maximum (Year) and the Minimum (Year)

          having Years as a (Dimension) Filter,

          but with the Year Dimension not present on a view.

           

          The above logic could be implemented as LOD expression(s).

          Please find the attached as an example (using Sample Superstore).

          Hope it could help a bit.

           

          Yours,

          Yuri