6 Replies Latest reply on Feb 11, 2019 6:54 AM by Jim Dehner

# Calculation with IF statement

Hi all,

I'm quite fresh in Tableau, and I can't figure out this relatively simple equation.

All I need to do is create calculated fields for different categories and get the counts for each category, for example sum of counts of "Apples":

Sum(IF[store1]="Apple" THEN [Store1 Count]END)+Sum(IF[Store2]="Apple" THEN [Store2 Count]END)+Sum(IF[Store3]="Apple" THEN [Store3 Count]END)+Sum(IF[Store4]="Apple" THEN [Store4 Count]END)+Sum(IF[Store5]="Apple" THEN [Store5 Count]END)

But it works only when a column actually has a value, but if a there's a column with no value the SUM of the equation becomes 0.

I know this must be very simple, I just can't figure it out.

I have made a test sample that represents the data I have:

 id store1 store1_count store2 store2_count store3 store3_count store4 store4_count store5 store5_count total count 1 Apple 2 Apple 4 Banana 6 Orange 2 Apple 10 24 2 Orange 3 Banana 2 Orange Pear 5 10 3 Banana 5 Pear 8 Apple Banana 4 17 4 Pear 10 Banana 5 Orange Banana 4 19

Thank you!

Best,
Linda

• ###### 1. Re: Calculation with IF statement

Try this:

IIF([store1]="Apple", [Store1 Count], 0) +

IIF([Store2]="Apple", [Store2 Count], 0) +

IIF([Store3]="Apple", [Store3 Count], 0) +

IIF([Store4]="Apple", [Store4 Count], 0) +

IIF([Store5]="Apple", [Store5 Count], 0)

Then aggregate via SUM when you place it on your view. • ###### 2. Re: Calculation with IF statement

Linda

Are you familiar with the tableau pivoting feature?  Your data is structured in an unusual way.  I would approach this by pivoting the data so you have essentially 3 colums - Store, Store Category, and Count.

Hunter

• ###### 3. Re: Calculation with IF statement

+1 for pivoting. But this calc should work if you can't. Note: ZN return zero if null.

SUM(

IF[store1]="Apple" THEN ZN([Store1 Count]) END +

IF[Store2]="Apple" THEN ZN([Store2 Count]) END +

IF[Store3]="Apple" THEN ZN([Store3 Count]) END +

IF[Store4]="Apple" THEN ZN([Store4 Count]) END +

IF[Store5]="Apple" THEN ZN([Store5 Count]) END)

• ###### 4. Re: Calculation with IF statement

Hi Linda

i used prep to restructure your data once in this form the results are a very easy to construct viz tableau has really good on-line training at Tableau Training: View Training Courses

see the sections on data prep and connections

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 5. Re: Calculation with IF statement

Hi Jim,

I hadn't used Tableau Prep before, but it turned out to be very useful and the best solution for this messy data set.

Cheers,Linda

• ###### 6. Re: Calculation with IF statement

Thanks Linda -

Prep was designed to clean up messy data - its a good tool - but if there is a chance to restructure the data at the source it is always a better solution