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

# Conditional Formula Help

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

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

Hey Walt,

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

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

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

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

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.