Calculating a value for current week and rolling that value for subsequent weeks

Prajju · September 2018

Hi,

I have the following 2 datasets:

Dataset A

Item  Location   Date                  X    Y 
 A      USA     Sep 10(Current Week)  10   10 
 A      USA     Sep 17                15   20          
 A      USA     Sep 24                10    5 
 A     Canada   Sep 10(Current Week)   0    4 
 A     Canada   Sep 17                 6    0 
 A     Canada   Sep 24                 13   15 
 B      USA     Sep 10(Current Week)   10   50 
 B      USA     Sep 17                 5    15 
 B      USA     Sep 24                 15   20

Dataset B:

  Item    Location     Z
   A        USA        15   
   A        Canada     0   
   B         USA       25

I need an output dataset as follows:

O/P

   Item   Location   Date   Final_Column
    A      USA      Sep 10     15    
    A      USA      Sep 17     20    
    A      USA      Sep 24     15    
    A    Canada     Sep 10      4    
    A    Canada     Sep 17      0   
    A    Canada     Sep 24      2    
    B      USA      Sep 10      65    
    B      USA      Sep 17      75    
    B      USA      Sep 24      80

The calculation is as follows:

   For current week, Final Column = (Z - X) + Y ----> 1st week value

For 2nd week, Final Column = (1st week value - X) + Y  ----> 2nd week value

For 3rd week, Final Column = (2nd week value - X) + Y  ----> 3rd week value

and so on ...

 Additionally, if value of Final Column < 0 then Final Column = 0.

How to achieve this calculation in MySQL?

Thanks in Advance.

Prajju · September 2018

Hi,

I came up with this

select a.item, a.location, a.date,
value = case when item <> a.item or location <> a.location then b.z - a.x + a.y
else value - a.x + a.y end final_column 
from  a inner join b on a.item = b.item and a.location = b.location,
(select value := 0, item := '', location := '') x
order by a.item, a.location, a.date

I am getting the correct values except for when the value of final column becomes less than 0.

When the final_column value goes less than 0 , it should be hard-coded to 0.

How to achieve this in the code above?

Thanks.

Unknown · September 2018

Hi, Prajju,

This is an interresting challenge! The part that makes it difficult is that, at each recursive step in the calculation, the final ouput must not be less than 0 (set to 0 if less than 0).

I did this in a SQL dataflow using two transforms. The first transform assigns a week order number, which is simply a row number partitioned by location and item and ordered by date. The second dataflow uses that week number as an input and calculates the final output value. I suppose it might be possible to combine those two steps into one, but this makes sense in my mind. Screen shot below.

The first transform:

SELECT
  xy.*,
  case when xy.`WeekNumber` = 1 then b.Z else 0 end as Z -- Z is only important in the week 1 calculation
from (
  SELECT
    a.Date,
    a.X,
    a.Y,
    case 
      when @PrevLocation = a.`Location` and @PrevItem = a.`Item` then @WeekNumber := @WeekNumber + 1 -- if the current record has the same location and item as the previous record, then @WeekNumber = @WeekNumber + 1; otherwise, this must be week 1
      else @WeekNumber := 1 
      end as WeekNumber,
@PrevLocation := a.`Location` as Location,
@PrevItem := a.`Item` as Item
  from 
    `dataset_a` as a,
    (select @WeekNumber := 0, @PrevLocation := '', @PrevItem := '') as init -- The From statement of the query is read first, so initialize variables here
  order by a.`Location`,a.`Item`,a.`Date` -- since the variables are evaluated in the order in which they appear in the select statement, ordering is important
) as xy
left join `dataset_b` as b
  on b.`Item` = xy.`Item`
  and b.`Location` = xy.`Location`

The second transform:

SELECT
  c.`Item`,
  c.`Location`,
  c.`Date`,
  case 
    when c.`WeekNumber` = 1 then @F := c.`Z` - c.`X` + c.`Y` -- if the current record is week 1, the final value F = Z - X + Y
    when c.`WeekNumber` > 1 and @F - c.`X` + c.`Y` > 0 then @F := @F - c.`X` + c.`Y` -- if the current record is week 2 or later (and if the previous value of F - current X + current Y > 0), final value is calculated recursively: F = F - X + Y
    when c.`WeekNumber` > 1 and @F - c.`X` + c.`Y` <= 0 then @F := 0 -- if the current record is week 2 or later (and if the previous value of F - current X + current Y <= 0), final value is F = 0
    end as final_column
from `weeknumbers` as c,
(select @F := 0) as init -- initialize the variable F
order by c.`Item`,c.`Location` desc,c.`WeekNumber`,c.`Date` -- again, since F is calculated incrementally, order matters

Hope this works for you.

Screen Shot 2018-09-18 at 12.14.35 PM.png

Prajju · September 2018

Hi Dan,

Thanks for your solution.

I actually came up with a relatively easier solution:

select a.item, a.location, a.date,
value = case when item <> a.item or location <> a.location then (ABS(b.z - a.x + a.y) + (b.z - a.x + a.y))/2
else (ABS(value - a.x + a.y) + (value - a.x + a.y))/2 end final_column 
from a inner join b on a.item = b.item and a.location = b.location, (select value := 0, item := '', location := '') x
order by a.item, a.location, a.date

So basically, if a value is negative, code it to 0. For example, if value = -15: (ABS(-15) + (-15)) / 2 --> (15 - 15)/2 ---> 0

If positive retain the value . If value = 15 : (ABS(15) + 15)/2 ---> 15

Thanks,

Prajwal

Calculating a value for current week and rolling that value for subsequent weeks

Comments

Categories