12 Replies Latest reply on Aug 13, 2018 9:54 PM by Ritesh Bisht

# Combining IF statement with LOD expression

Hi Tableau community,

I am struggling to create a calculation that combines an IF statement with an LOD expression.

I've looked through several posts on the forum, but can't find anything that works for me.

I'm trying to analyse the new product sales, which we defined as:

"The summed sales of the first 12 months a customer orders a new product"

If a customer orders strawberries for the first time on January 20th 2017, his/her orders should count as new product sales until January 19th 2018.

This is what I tried, but I can't get it to work.

IF

{ FIXED [Customer], [Product] :

(DATEDIFF('day', [First Order Product], [Ship Date]<365))

}

THEN "New Product sales" else "Not New Product sales" END

I hope the attached workbook clarifies. Thanks!

• ###### 1. Re: Combining IF statement with LOD expression

Hi Van,

Do you mean like this ?

Just copy paste this ------>

if DATEDIFF('year' , [First Order Product] , [Date]) >= 1

then "Not New Product sales" else "New Product sales"

END

Thanks,

Ritesh

Please mark the answer as CORRECT/HELPFUL if it really helped you so that it can help others as well.

1 of 1 people found this helpful
• ###### 2. Re: Combining IF statement with LOD expression

Almost, but "new product sales" should be for a specific customer, for a specific product.

That's why I'm trying to include FIXED Customer, Product.

• ###### 3. Re: Combining IF statement with LOD expression

Hi, Michiel

Hope this helps

ZZ

• ###### 4. Re: Combining IF statement with LOD expression

Hi ZZ, thanks for your help.

It's also not exactly what I'm looking for.

New Product Sales is for a specific customer, for a specific product:

• If Dave buys bananas on March 6th, 2017 for the first time, his banana orders should be New Product Sales until March 5th, 2018.
• If Dave buys strawberries on January 5th, 2017 for the first time, his strawberry orders should be New Product Sales until January 4th, 2018.
• If Gwen buys strawberries in February 19th, 2017 for the first time, her strawberry orders should be New Product Sales until February 18th, 2018.

I created the right answers in Excel. I hope someone can help me creating the formula in Tableau.

• ###### 5. Re: Combining IF statement with LOD expression

Hi Michiel,

If a customer orders strawberries for the first time on January 20th 2017, his/her orders should count as new product sales until January 19th 2018.

Let's look at Dave and Strawberries:

Dave purchases strawberries for the first time on 1/5/2016. The order counts as a new product until 1/4/2017 (exactly 365 days after the first order).

For all purchases between the first order and the 'first order cut-off date', we call it "New Product Sales"

I believe Ritesh has a similar solution. Could you clarify how this differs from what you are looking for?

Thanks,

Ray

1 of 1 people found this helpful
• ###### 6. Re: Combining IF statement with LOD expression

Oh got it,

Just add two more steps with the help of below screenshots

Duplicate your First order date & make it last order date as below

Now just replace older date with the Last Order date

Guess this is the desired output.

Thanks,

Ritesh

• ###### 7. Re: Combining IF statement with LOD expression

Hi Ritesh, thanks again.

Sorry, but no, not the desired output. As you can see from the screenshot and workbook I attached, the orders from a specific customer, from a specific product, should be marked as New Product Sales for the first 12 months.

In your outcome, this is not the case. I don't know how to make myself more clear than this:

• If Dave buys bananas on March 6th, 2017 for the first time, his banana orders should be marked as New Product Sales until March 5th, 2018.
• If Dave buys strawberries on January 5th, 2017 for the first time, his strawberry orders should be marked as New Product Sales until January 4th, 2018.
• If Gwen buys strawberries in February 19th, 2017 for the first time, her strawberry orders should be marked as New Product Sales until February 18th, 2018.
• ###### 8. Re: Combining IF statement with LOD expression

Hi, Michiel

In this case, you just need remove your fixed statement in your calculation and something similar as below

Hope this helps

ZZ

• ###### 9. Re: Combining IF statement with LOD expression

Hi Ray,

Didn't see your response until now. You're right! Ritesh reply is correct. I guess it's more simple than I thought.

I thought the FIXED customer & product had to be included in the calculation, but it's not necessary.

Thanks for noticing and letting me know!

• ###### 10. Re: Combining IF statement with LOD expression

Thanks ZZ. Ray pointed out that Ritesh initial response was correct already. Thanks for your help!