5 Replies Latest reply on Feb 22, 2018 3:43 PM by sunil.sachdeva

# Subtraction of overallocation from day before

Hello everyone,

I am pretty new in working with Tableau ( 1month) and i can't come up with a solution for my problem. I know it will be better to add few data, but unfortunately i can't share it.

Data: from different origin of goods and different destinations there is a limit per week and per day of how much weight is possible to have.

For example. Origin Russia and destination FR is 100Ton, Origin Russia and destination USA is 80ton, Origin China and destination USA is 150ton etc. etc.

Calculation Field (1)  done as following:  IF [Origin] AND [Destination] THEN 100

ELSE IF [Origin] AND [Destination] THEN 200

Calculation Field (1) in Reference Line as Minimum. this is the bar chart that i got (just 2 lines as example):

Dest. country - axes Weight (in ton) and the reference line showing the limit. This Table is ok.

1. Question: I would like to have in red the one that the limit for the week has been over exceed. I tried to do calculation: [weight]>calculation field (1) THEN 0 ELSE 1 but it doesn't work. it just give me 1 while there should be also 0. How can I do it?

Data: Limitation per day is kind similar to the week limitation but if for example Origin Russia and Destination USA we have per day 50 ton max of weight:

Mon 45 ton

Tue 55 ton

Wed (we will have only 45ton available and not 50 because the day before we got more then the limit per day).

2. Question: I would like to show when per day we exceed the limit but considering that they have to: [day before exceed limit] - [the limit per day]. Following the example before 55 (of tuesday) - 50 (the limit per day)=5 on Wed i have as a limit 45. I tried to do the calculation but instead of doing the sum[weight]*that day (only monday for example). it did it per each rows.

3. Question: there is a way to show in tableau on the axis week day, where for every origin and destination, showing me in which day the limit has exceed (grey colour no, red one exceed) and also a line on when in that week the limit weight per week has been exceed too?

I imagined like this:

I hope my questions are clear. I really appreciate anyone who would like to help me.

Thank you,

Giulia

• ###### 1. Re: Subtraction of overallocation from day before

Hi,

looking at your questions, this can be done in Tableau, but it really depends on how your data is structured, i understand data sensitivity but it would be helpful if you can mockup data with dummy values and share sample.

get rid of real data points and add dummy values and share something to start with.

Thanks,

• ###### 2. Re: Subtraction of overallocation from day before

sunil.sachdeva wrote:

Hi,

looking at your questions, this can be done in Tableau, but it really depends on how your data is structured, i understand data sensitivity but it would be helpful if you can mockup data with dummy values and share sample.

get rid of real data points and add dummy values and share something to start with.

Thanks,

Hello Sunil,

Thank you for your reply and sorry for the late answer.

Please find attached an example with data.

Thank you for your support,

Giulia

• ###### 3. Re: Subtraction of overallocation from day before

Hi,

sorry for delay in response as got busy with other priorities:

are you after something like this?

1. Question: I would like to have in red the one that the limit for the week has been over exceed. I tried to do calculation: [weight]>calculation field (1) THEN 0 ELSE 1 but it doesn't work. it just give me 1 while there should be also 0. How can I do it?

2. Question: I would like to show when per day we exceed the limit but considering that they have to: [day before exceed limit] - [the limit per day]. Following the example before 55 (of tuesday) - 50 (the limit per day)=5 on Wed i have as a limit 45. I tried to do the calculation but instead of doing the sum[weight]*that day (only monday for example). it did it per each rows.

3. Question: there is a way to show in tableau on the axis week day, where for every origin and destination, showing me in which day the limit has exceed (grey colour no, red one exceed) and also a line on when in that week the limit weight per week has been exceed too?

I imagined like this:

Please find attached sample workbook, hope that helps.

• ###### 4. Re: Subtraction of overallocation from day before

Hi Sunil,

Thank you very much for your answer.

The first point worked out perfectly!.

2. in the example you send me is showing like following: (Day Limit of yesterday - Weight of yesterday) + Day limit of today.

Considering that the day limit given a Destination and a origin is always the same.

i need the following: IF TODAY does not exceed the limit per day THEN "OK"

ELSEIF TODAY does exceed the limit THEN TOMORROW LIMIT is: (LIMIT x day) - (Today Weight - Limit Per day).

example. Origin Australia, destination Group 2 limit is 200.

Monday - (Limit is 200) --> Weight i have is 150. Limit is not exceed.

Tuesday - (Limit is 200) --> Weight I have is 250. Limit is exceed.

Wednes - (Limit is 150 because the day before i exceed the limit for 50) --> Weight I have is 100. Limit of 150 not exceed.

Thursday- (Limit is 200) etc etc.

The calculation need to be done based on the SUM of weight of Origin and Destination, not per row.

I hope is more clear now and that you can still help me to recreate this in Tableau.

3. I don't understand where is the Week Limit Line?

Thank you for your help,

Giulia

• ###### 5. Re: Subtraction of overallocation from day before

Hi,

Thanks for detailed explanation, hope i understood correctly and check below vizs

Question 2:

and Question 3:

Please find attached sample workbook.