15 Replies Latest reply on Aug 6, 2016 12:23 PM by Tom W

# Look up of a value. if it has then sum all the numbers

Hello All,

I have attached an excel file. I want the below scenario.

1) If Lifecycle for any product has "include" and shipment from demand.

2) if Lifecycle for any product has both "include" and "exclude" then minus shipment from demand which having only include lifecycle.

3) If lifecycle is exclude then there will be 0.

• ###### 1. Re: Look up of a value. if it has then sum all the numbers

It's not really clear what you're asking. Do you want to apply a filter for each scenario?

Can you please reference the actual column names and perhaps provide some examples of your expected results for each?

• ###### 2. Re: Look up of a value. if it has then sum all the numbers

Hello Tom,

When you try two filter each product. You will find three scenarios in lifecycle column  :

1) Exclude - Don't want to get difference of shipment vs Demand

2) Include - Calculate difference of shipment vs Demand

3) Exclude and Include - difference of shipment vs Demand (total of exclude+include)

Please let me know, if you want some more details. Updated file are attached.

• ###### 3. Re: Look up of a value. if it has then sum all the numbers

Akhilesh,

I still don't see a question, a problem statement or even expected results. I'm not really sure what you need help with.

• ###### 4. Re: Look up of a value. if it has then sum all the numbers

Hello Tom,

We can have chat on my skype id is rajputonnet or hangout rajputonnet@gmail.com.

-Akhilesh

• ###### 5. Re: Look up of a value. if it has then sum all the numbers

I want the difference of shipment-demand having the grand total APC Description Wise on the basis of Lifecycle.

1) Exclude - Don't want to get difference of shipment vs Demand

2) Include - Calculate difference of shipment vs Demand

3) Exclude and Include - difference of shipment vs Demand (total of exclude+include)

• ###### 6. Re: Look up of a value. if it has then sum all the numbers

I wont be able to call you on Skype, I volunteer my time to help out here on the forums - I'm not paid support! If you need hands on training and support, Tableau do have a number of paid option you might want to consider.

Your dot points really aren't descriptive enough for me to be able to help you end to end. I've attached an example which shows how to calculate the difference between Demand Shipping, perhaps you can take it the rest of the way or ask some specific questions to help get your further.

• ###### 7. Re: Look up of a value. if it has then sum all the numbers

I know Tom. . That's perfectly alright. But can you plz share that example which you were saying to attach.

• ###### 8. Re: Look up of a value. if it has then sum all the numbers

It is attached to my previous post, "shipment.twbx"

• ###### 9. Re: Look up of a value. if it has then sum all the numbers

Hello Tom,

I have tried to make you understand. I hope now i am successful. . Below is the calculation of scenarios and data file is also attached for reference.

• ###### 10. Re: Look up of a value. if it has then sum all the numbers

Please find attached, I've implemented using three calculated fields.

You won't be able to get the output as you've laid it out in Excel for a number of reasons;

1. In Excel you seem to have multiple columns to the right of shipment doing different things. i.e the first column to the right of shipment does a sum of demand in the 2nd row, but in the third row it does the same calculation as the orange column 2 across in the row above. In Tableau these need to be consistent.

2. You can't show your demand / shipment metrics broken out by lifecycle then 'merge' cells for the results of the calculations. It isn't possible. As per my example, I've removed lifecycle from the report to make the result possible. If you need lifecycle in the report the only options you have are to duplicate the result of 867500 across two rows or show the calculation on a row by row basis.

1 of 1 people found this helpful
• ###### 11. Re: Look up of a value. if it has then sum all the numbers

Hello Tom,

Thank you for your kind support. It's help me a lot.

I tried to update this with my real scenario. Unfortunately stuck. I have created duplicates of your calculated fields as below Apc Description replaced by Brand:

But getting, different results. I have attached my dashboard. Please have a look and help me.

• ###### 12. Re: Look up of a value. if it has then sum all the numbers

You are comparing two different levels of aggregation.

The APC Description sheet includes the APC Description dimension but you aren't including that in the brand sheet, so you're comparing two different things.

You would need to change the level of detail calcs to force the aggregation to a different level perhaps.

1 of 1 people found this helpful
• ###### 13. Re: Look up of a value. if it has then sum all the numbers

Hello Tom,

Thank you, i have done that later. But from Different way and getting the desired result.

But, going on further calculation. I want consolidated summary and stuck. Like in Header summary Sheet, there is Zero in Error.

Calculation for Error is Total sum of Abs.% Error x (A)(3) x (6)/shipment having Abs.% Error x (A)(3) x (6) not equals to Zero.

For: Abs.% Error x (A)(3) x (6) not equals to Zero.

For: Error

I have attached the updated file. Please have a look and help to get where i am going wrong.

• ###### 14. Re: Look up of a value. if it has then sum all the numbers

You need to walk back through each of your calculations which contribute to the 'Error' calculation. If you start to break those down, you'll see they are all producing zero all the way back to 'ErrorA-F(4)'. It references ATTR([Lifecycle]) but you don't use this dimension in your report at all. Thus, this calc will always produce zero.

I'm not 100% sure what you're trying to do with this field, but I assume you'll need to use table calculations like my original example.