2 Replies Latest reply on Apr 13, 2018 9:37 PM by Philip George

Calculate a value sequentially based on another field value.

Hi,

Please find the attached item for a sample data set. below is a snapshot of the same. I am trying to find out the Target value  using Tableau. can some one please help me to get this calculation. I have given the explanation of the target calculation in the attached excel.

 Rptdate Item SLA Week start Date Actual Target 4/12/2018 Item1 15 4/9/2018 370 100% 4/12/2018 Item1 15 4/16/2018 302 100% 4/12/2018 Item1 15 4/23/2018 335 57.14286 4/12/2018 Item1 15 4/30/2018 241 0 4/12/2018 Item1 15 5/7/2018 375 0 4/12/2018 Item1 15 5/14/2018 298 0 4/12/2018 Item1 15 5/21/2018 258 0 4/12/2018 Item1 15 5/28/2018 384 0 4/12/2018 Item1 15 6/4/2018 275 0 4/12/2018 Item1 15 6/11/2018 367 0 4/12/2018 Item1 15 6/18/2018 381 0

Regards,

philip

• 1. Re: Calculate a value sequentially based on another field value.

Hi Philip,

Please can you elaborate as to what it is you are trying to do. Checking your attached spreadsheet has no further indication of what it is you are trying to do, aside from a hard-coded calculation of (4/7)*100

Steve

• 2. Re: Calculate a value sequentially based on another field value.

Hi Steve,

I will explain the logic in detail.

.

attached spredsheet is for the date 4/12/2018. my week start date is every monday which is starting 4/9/2018. number of days left in the current week is 4.

My SLA is 15 for that item. means the first 15 days i have to deliver something without fail. that is my target percentage.

first row: only 4 days are left in the week and i have to deliver completely for the week. so my target for the week is 100%.

(15-4=11). Now I have 11 days left in the SLA.

second row:  7 days in the week and i have 11 days left in the SLA. so I have to  deliver completely for the week. so my target is 100%.

(11-7=4). Now i have 4 days left in the SLA.

Third row: 7 days in the week and I have only 4 days left in the SLA. so I have to deliver only for the 4 days. so my target is 4/7 =.57

rest of the rows can be 0.

I hope this is clear and thanks for your help.

Regards,

philip