User Guide

Getting Started

Help Center

Documentation

Community

Training

Certification

Question

Conditional Calculations with LAG Functions

  • 19 July 2017
  • 4 replies
  • 438 views
D

I’m curious if anyone has any ideas on how to solve the problem in the picture below. We want to try and calculate future inventory based on the projected sales and previous inventory but not allow for negative inventory and essentially ignore projected sales when there is no inventory.


The data is stored in Redshift and we’ve tried to use the SUM Window function but this fails when you potentially have multiple occurrences of stock-outs as the SUM Window doesn’t calculate inventory position correctly when new inventory takes it above zero.



Here is the Excel formula (Column F) for the running inventory:

F3 = B3+C3-D3 (this is the first row of running inventory)

F4 = IF(F3+C4-D4<0,0,F3+C4-D4) - for all subsequent rows this is the formula. The challenge is that it references the prior row result

4 replies

Morgan
Rank
Userlevel 3
Badge

Hey @DanLeBlanc,


Can you post the excel formula for reference?


Thanks!

D

Posted - thanks

S

@DanLeBlanc I’ve slightly modified the solution outlined in this article https://stackoverflow.com/a/23020788/2419252. I certainly cannot take credit for this pattern. Hopefully you find it useful.


create or replace table inventory (

  sales_date date

  , current_inventory int

  , incoming_inventory int

  , sales int

);



insert into inventory

values 

    ('2017-01-01', 250, 0, 25)

  , ('2017-01-02', 0, 0, 50)

  , ('2017-01-03', 0, 0, 30)

  , ('2017-01-04', 0, 0, 40)

  , ('2017-01-05', 0, 0, 25)

  , ('2017-01-06', 0, 0, 10)

  , ('2017-01-07', 0, 0, 50)

  , ('2017-01-08', 0, 0, 60)

  , ('2017-01-09', 0, 0, 20)

  , ('2017-01-10', 0, 100, 40)

  , ('2017-01-11', 0, 0, 20)

  , ('2017-01-12', 0, 0, 15)

  , ('2017-01-13', 0, 0, 20)

  , ('2017-01-14', 0, 0, 30)

  , ('2017-01-15', 0, 0, 25)

  , ('2017-01-16', 0, 0, 30)

  , ('2017-01-17', 0, 300, 20)

  , ('2017-01-17', 0, 0, 40);





with running_sum as (

  select *

    , sum(current_inventory + incoming_inventory - sales) over(order by sales_date rows unbounded preceding) as running_inventory

  from inventory

)

, running_replenishment as (

  select *

    , case

        when min(running_inventory) over(order by sales_date rows unbounded preceding) < 0

          then -1 * min(running_inventory) over(order by sales_date rows unbounded preceding)

        else 0

      end as replenish_amount

  from running_sum

)

select sales_date

  , running_inventory + replenish_amount as adjusted_running_inventory

from running_replenishment

;
D

Worked like a charm - thanks for the help!!

I thought I was going to have to build a process outside of Looker to do a recursive update or use cursors. Awesome!

Reply