6 Replies Latest reply on Dec 7, 2016 1:14 AM by Daniel Martins

    Conditional Formula Help

    Daniel Martins

      Hey Guys,

       

      I do have a lot of problems with the formulas in Tableau.

       

      I am using the "order" data of a product as the Columns view.

      Now, i am filtering out Cancellations when displaying the data.

      However, If an Order is cancelled, a couple of months later it is currently simply accounted to the date when it was ordered.

      (Cancellations that we receive in August for orders that took place in January are accounted to January)

      But i would like to have the cancellation be accounted to the month when it was cancelled.

      (Cancellations that  we receive in august for orders that took place in January should be accounted to August)

       

      Therefore i though about a formula for the order dimension that work somehow like this:

       

      IF the Cancellations date is not blank the use cancellation date as Order date.

       

      Is something like this possible?

      Thanks a lot !

      Daniel

        • 1. Re: Conditional Formula Help
          Walt Reed

          Hey Daniel,

          Based on my understanding of what you want, I think you could create a formula like:

          IF ISNULL([Cancellations Date]) THEN [Order Date] ELSE [Cancellations Date] END

           

          Try this out and let us know if it works.

           

          Best,

          Walt

          • 2. Re: Conditional Formula Help
            Daniel Martins

            Hey Walt,

             

            First of all thanks for your quick reply!

             

            I should mention that i am using the formula in Tableau for the very first time before stating my problem

            I tried to type in the formula in my column - dimension "order" ,  as condition - by formula.

            However, i receive the error message "the formula must be an aggregated calculation or refer only to this field"

            Now i have no idea how to make the formula work.

             

            MY Workbook looks like this:

            Columns : Order (it is broken down to months)

            Rows : Sum (number of records)

             

            Filters : Cancellation

             

            Hope you have suggestion how to make the formula work.

            Thanks a lot!

            • 3. Re: Conditional Formula Help
              Walt Reed

              Hey Daniel,

              See my attached workbook. Instead of using any filters, the new field will create the new date to be displayed.

               

              Is this what you wanted?

              If so, New Field is defined as:

              Then, when you're making your table, take off Order Date and Cancellations Date, so that only New Field is showing.

               

              Walt

              • 4. Re: Conditional Formula Help
                Daniel Martins

                Sorry i do still have anther issue related to this.

                 

                I have "Stages" in my Data.  with  "ordered" and "cancellation"

                 

                Now, your previous solution worked perfectly to account the cancellations to the correct month.

                But i would like to have "=orders - Cancellations" in the month in order to see the NET orders

                 

                Is it possible to include this in the calculated field?

                I tried to create a new calculated field for the number of records but i am not able to select the diffrent types of stages.

                Do you have know how to fix this as well?

                 

                Thanks a lot Walt!!

                • 5. Re: Conditional Formula Help
                  Walt Reed

                  Hey Daniel,

                  I'd be glad to help with this. Is there a packaged workbook or Excel file you could attach so that I can play with the data?

                   

                  Walt

                  • 6. Re: Conditional Formula Help
                    Daniel Martins

                    Hey Walt,

                     

                    As your first solution work like a charm i just marked this as the correct answer for my initial question.

                    thanks a lot!!

                     

                    i created a new thread for the calculation issue.

                    Many thanks for your efforts!!

                    Daniel